/* orafce--3.17.sql */

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION orafce" to load this file. \quit

CREATE FUNCTION pg_catalog.months_between(date1 date, date2 date)
RETURNS numeric
AS '$libdir/whale', 'months_between'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION pg_catalog.months_between(date, date) IS 'returns the number of months between date1 and date2';

CREATE FUNCTION pg_catalog.round(value timestamp with time zone, fmt text)
RETURNS timestamp with time zone
AS '$libdir/whale','ora_timestamptz_round'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION pg_catalog.round(timestamp with time zone, text) IS 'round dates according to the specified format';

CREATE FUNCTION pg_catalog.round(value timestamp with time zone)
RETURNS timestamp with time zone
AS $$ SELECT pg_catalog.round($1, 'DDD'); $$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION pg_catalog.round(timestamp with time zone) IS 'will round dates according to the specified format';

CREATE FUNCTION pg_catalog.nlssort(text)
RETURNS text
AS $$ SELECT pg_catalog.nlssort($1, null); $$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION pg_catalog.nlssort(text)IS '';

CREATE FUNCTION pg_catalog.set_nls_sort(text)
RETURNS void
AS '$libdir/whale', 'ora_set_nls_sort'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION pg_catalog.set_nls_sort(text) IS '';

CREATE FUNCTION to_multi_byte(str text)
RETURNS text
AS '$libdir/whale','orafce_to_multi_byte'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION to_multi_byte(text) IS 'Convert all single-byte characters to their corresponding multibyte characters';

CREATE FUNCTION to_single_byte(str text)
RETURNS text
AS '$libdir/whale','orafce_to_single_byte'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION to_single_byte(text) IS 'Convert characters to their corresponding single-byte characters if possible';

CREATE FUNCTION sinh(float8)
RETURNS float8 AS
$$ SELECT (exp($1) - exp(-$1)) / 2; $$
LANGUAGE sql IMMUTABLE STRICT;

CREATE FUNCTION cosh(float8)
RETURNS float8 AS
$$ SELECT (exp($1) + exp(-$1)) / 2; $$
LANGUAGE sql IMMUTABLE STRICT;

CREATE FUNCTION tanh(float8)
RETURNS float8 AS
$$ SELECT sinh($1) / cosh($1); $$
LANGUAGE sql IMMUTABLE STRICT;

CREATE FUNCTION nanvl(float4, float4)
RETURNS float4 AS
$$ SELECT CASE WHEN $1 = 'NaN' THEN $2 ELSE $1 END; $$
LANGUAGE sql IMMUTABLE STRICT;

CREATE FUNCTION nanvl(float8, float8)
RETURNS float8 AS
$$ SELECT CASE WHEN $1 = 'NaN' THEN $2 ELSE $1 END; $$
LANGUAGE sql IMMUTABLE STRICT;

CREATE FUNCTION nanvl(numeric, numeric)
RETURNS numeric AS
$$ SELECT CASE WHEN $1 = 'NaN' THEN $2 ELSE $1 END; $$
LANGUAGE sql IMMUTABLE STRICT;

CREATE FUNCTION nanvl(float4, varchar)
RETURNS float4 AS
$$ SELECT CASE WHEN $1 = 'NaN' THEN $2::float4 ELSE $1 END; $$
LANGUAGE sql IMMUTABLE STRICT;

CREATE FUNCTION nanvl(float8, varchar)
RETURNS float8 AS
$$ SELECT CASE WHEN $1 = 'NaN' THEN $2::float8 ELSE $1 END; $$
LANGUAGE sql IMMUTABLE STRICT;

CREATE FUNCTION nanvl(numeric, varchar)
RETURNS numeric AS
$$ SELECT CASE WHEN $1 = 'NaN' THEN $2::numeric ELSE $1 END; $$
LANGUAGE sql IMMUTABLE STRICT;

CREATE FUNCTION dump("any")
RETURNS varchar
AS '$libdir/whale', 'orafce_dump'
LANGUAGE C;

CREATE FUNCTION dump("any", integer)
RETURNS varchar
AS '$libdir/whale', 'orafce_dump'
LANGUAGE C;


CREATE FUNCTION pg_catalog.lnnvl(bool)
RETURNS bool
AS '$libdir/whale','ora_lnnvl'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION pg_catalog.lnnvl(bool) IS '';

-- can't overwrite PostgreSQL functions!!!!

CREATE SCHEMA orafce;

CREATE FUNCTION orafce.months_between(TIMESTAMP WITH TIME ZONE,TIMESTAMP WITH TIME ZONE)
RETURNS NUMERIC
AS $$ SELECT pg_catalog.months_between($1::pg_catalog.date,$2::pg_catalog.date); $$
LANGUAGE SQL IMMUTABLE STRICT;

CREATE FUNCTION orafce.months_between(TIMESTAMP WITHOUT TIME ZONE,TIMESTAMP WITHOUT TIME ZONE)
RETURNS NUMERIC
AS $$ SELECT pg_catalog.months_between($1::pg_catalog.date,$2::pg_catalog.date); $$
LANGUAGE SQL IMMUTABLE STRICT;

CREATE FUNCTION orafce.substr(str text, start_arg int)
RETURNS text
AS '$libdir/whale','orafce_substr2'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION orafce.substr(text, int) IS 'Returns substring started on start_in to end';

CREATE FUNCTION orafce.substr(str text, start_arg int, len int)
RETURNS text
AS '$libdir/whale','orafce_substr3'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION orafce.substr(text, int, int) IS 'Returns substring started on start_in len chars';

CREATE OR REPLACE FUNCTION orafce.substr(numeric,numeric)
RETURNS text AS $$
SELECT orafce.substr($1::text,trunc($2)::int);
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION orafce.substr(numeric,numeric,numeric)
RETURNS text AS $$
SELECT orafce.substr($1::text,trunc($2)::int,trunc($3)::int);
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION orafce.substr(varchar,numeric)
RETURNS text AS $$
SELECT orafce.substr($1,trunc($2)::int);
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION orafce.substr(varchar,numeric,numeric)
RETURNS text AS $$
SELECT orafce.substr($1,trunc($2)::int,trunc($3)::int);
$$ LANGUAGE SQL IMMUTABLE;

--can't overwrite PostgreSQL DATE data type!!!
CREATE FUNCTION orafce.to_char(timestamp)
RETURNS TEXT
AS '$libdir/whale','orafce_to_char_timestamp'
LANGUAGE C STABLE STRICT;
COMMENT ON FUNCTION orafce.to_char(timestamp) IS 'Convert timestamp to string';


CREATE FUNCTION orafce.sessiontimezone()
RETURNS text
AS '$libdir/whale','orafce_sessiontimezone'
LANGUAGE C STABLE STRICT;
COMMENT ON FUNCTION orafce.sessiontimezone() IS 'Ruturns session time zone';

CREATE FUNCTION orafce.dbtimezone()
RETURNS text
AS '$libdir/whale','orafce_dbtimezone'
LANGUAGE C STABLE STRICT;
COMMENT ON FUNCTION orafce.dbtimezone() IS 'Ruturns server time zone (orafce.timezone)';

-- emulation of dual table
CREATE VIEW public.dual AS SELECT 'X'::varchar AS dummy;
REVOKE ALL ON public.dual FROM PUBLIC;
GRANT SELECT, REFERENCES ON public.dual TO PUBLIC;

-- this packege is emulation of gms_output Orafce packege

CREATE SCHEMA gms_output;

CREATE FUNCTION gms_output.enable(IN buffer_size int4)
RETURNS void
AS '$libdir/whale','gms_output_enable'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION gms_output.enable(IN int4) IS 'Enable package functionality';

CREATE FUNCTION gms_output.enable()
RETURNS void
AS '$libdir/whale','gms_output_enable_default'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_output.enable() IS 'Enable package functionality';

CREATE FUNCTION gms_output.disable()
RETURNS void
AS '$libdir/whale','gms_output_disable'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_output.disable() IS 'Disable package functionality';

CREATE FUNCTION gms_output.serveroutput(IN bool)
RETURNS void
AS '$libdir/whale','gms_output_serveroutput'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_output.serveroutput(IN bool) IS 'Set drowing output';

CREATE FUNCTION gms_output.put(IN a text)
RETURNS void
AS '$libdir/whale','gms_output_put'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_output.put(IN text) IS 'Put some text to output';

CREATE FUNCTION gms_output.put_line(IN a text)
RETURNS void
AS '$libdir/whale','gms_output_put_line'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_output.put_line(IN text) IS 'Put line to output';

CREATE FUNCTION gms_output.new_line()
RETURNS void
AS '$libdir/whale','gms_output_new_line'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_output.new_line() IS 'Put new line char to output';

CREATE FUNCTION gms_output.get_line(OUT line text, OUT status int4)
AS '$libdir/whale','gms_output_get_line'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_output.get_line(OUT text, OUT int4) IS 'Get line from output buffer';


CREATE FUNCTION gms_output.get_lines(OUT lines text[], INOUT numlines int4)
AS '$libdir/whale','gms_output_get_lines'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_output.get_lines(OUT text[], INOUT int4) IS 'Get lines from output buffer';


-- others functions
CREATE FUNCTION "nvl"(anyelement, anyelement)
RETURNS anyelement
AS '$libdir/whale','ora_nvl'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION "nvl"(anyelement, anyelement) IS '';

CREATE FUNCTION nvl2(anyelement, anyelement, anyelement)
RETURNS anyelement
AS '$libdir/whale','ora_nvl2'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION nvl2(anyelement, anyelement, anyelement) IS '';

CREATE FUNCTION public.decode(anyelement, anyelement, text)
RETURNS text
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, text, text)
RETURNS text
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, text, anyelement, text)
RETURNS text
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, text, anyelement, text, text)
RETURNS text
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, text, anyelement, text, anyelement, text)
RETURNS text
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, text, anyelement, text, anyelement, text, text)
RETURNS text
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, bpchar)
RETURNS bpchar
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, bpchar, bpchar)
RETURNS bpchar
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, bpchar, anyelement, bpchar)
RETURNS bpchar
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, bpchar, anyelement, bpchar, bpchar)
RETURNS bpchar
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, bpchar, anyelement, bpchar, anyelement, bpchar)
RETURNS bpchar
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, bpchar, anyelement, bpchar, anyelement, bpchar, bpchar)
RETURNS bpchar
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, integer)
RETURNS integer
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, integer, integer)
RETURNS integer
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, integer, anyelement, integer)
RETURNS integer
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, integer, anyelement, integer, integer)
RETURNS integer
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, integer, anyelement, integer, anyelement, integer)
RETURNS integer
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, integer, anyelement, integer, anyelement, integer, integer)
RETURNS integer
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, bigint)
RETURNS bigint
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, bigint, bigint)
RETURNS bigint
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, bigint, anyelement, bigint)
RETURNS bigint
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, bigint, anyelement, bigint, bigint)
RETURNS bigint
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, bigint, anyelement, bigint, anyelement, bigint)
RETURNS bigint
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, bigint, anyelement, bigint, anyelement, bigint, bigint)
RETURNS bigint
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, numeric)
RETURNS numeric
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, numeric, numeric)
RETURNS numeric
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, numeric, anyelement, numeric)
RETURNS numeric
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, numeric, anyelement, numeric, numeric)
RETURNS numeric
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, numeric, anyelement, numeric, anyelement, numeric)
RETURNS numeric
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, numeric, anyelement, numeric, anyelement, numeric, numeric)
RETURNS numeric
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;


CREATE FUNCTION public.decode(anyelement, anyelement, time)
RETURNS time
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, time, time)
RETURNS time
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, time, anyelement, time)
RETURNS time
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, time, anyelement, time, time)
RETURNS time
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, time, anyelement, time, anyelement, time)
RETURNS time
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, time, anyelement, time, anyelement, time, time)
RETURNS time
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, timestamp)
RETURNS timestamp
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, timestamp, timestamp)
RETURNS timestamp
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, timestamp, anyelement, timestamp)
RETURNS timestamp
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, timestamp, anyelement, timestamp, timestamp)
RETURNS timestamp
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, timestamp, anyelement, timestamp, anyelement, timestamp)
RETURNS timestamp
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, timestamp, anyelement, timestamp, anyelement, timestamp, timestamp)
RETURNS timestamp
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, timestamptz)
RETURNS timestamptz
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, timestamptz, timestamptz)
RETURNS timestamptz
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, timestamptz, anyelement, timestamptz)
RETURNS timestamptz
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, timestamptz, anyelement, timestamptz, timestamptz)
RETURNS timestamptz
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, timestamptz, anyelement, timestamptz, anyelement, timestamptz)
RETURNS timestamptz
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION public.decode(anyelement, anyelement, timestamptz, anyelement, timestamptz, anyelement, timestamptz, timestamptz)
RETURNS timestamptz
AS '$libdir/whale', 'ora_decode'
LANGUAGE C IMMUTABLE;

CREATE SCHEMA gms_utility;

CREATE FUNCTION gms_utility.format_call_stack(text)
RETURNS text
AS '$libdir/whale','gms_utility_format_call_stack1'
LANGUAGE C STRICT VOLATILE;
COMMENT ON FUNCTION gms_utility.format_call_stack(text) IS 'Return formated call stack';

CREATE FUNCTION gms_utility.format_call_stack()
RETURNS text
AS '$libdir/whale','gms_utility_format_call_stack0'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION gms_utility.format_call_stack() IS 'Return formated call stack';

CREATE FUNCTION gms_utility.get_time()
RETURNS int
AS '$libdir/whale','gms_utility_get_time'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION gms_utility.get_time() IS 'Returns the number of hundredths of seconds that have elapsed since point in time';


CREATE SCHEMA utl_file;
CREATE DOMAIN utl_file.file_type integer;

CREATE FUNCTION utl_file.fopen(location text, filename text, open_mode text, max_linesize integer, encoding name)
RETURNS utl_file.file_type
AS '$libdir/whale','utl_file_fopen'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.fopen(text,text,text,integer,name) IS 'The FOPEN function open file and return file handle';

CREATE FUNCTION utl_file.fopen(location text, filename text, open_mode text, max_linesize integer)
RETURNS utl_file.file_type
AS '$libdir/whale','utl_file_fopen'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.fopen(text,text,text,integer) IS 'The FOPEN function open file and return file handle';

CREATE FUNCTION utl_file.fopen(location text, filename text, open_mode text)
RETURNS utl_file.file_type
AS $$SELECT utl_file.fopen($1, $2, $3, 1024); $$
LANGUAGE SQL VOLATILE;
COMMENT ON FUNCTION utl_file.fopen(text,text,text,integer) IS 'The FOPEN function open file and return file handle';

CREATE FUNCTION utl_file.is_open(file utl_file.file_type)
RETURNS bool
AS '$libdir/whale','utl_file_is_open'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.is_open(utl_file.file_type) IS 'Functions returns true if handle points to file that is open';

CREATE FUNCTION utl_file.get_line(file utl_file.file_type, OUT buffer text)
AS '$libdir/whale','utl_file_get_line'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.get_line(utl_file.file_type) IS 'Returns one line from file';

CREATE FUNCTION utl_file.get_line(file utl_file.file_type, OUT buffer text, len integer)
AS '$libdir/whale','utl_file_get_line'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.get_line(utl_file.file_type, len integer) IS 'Returns one line from file';

CREATE FUNCTION utl_file.get_nextline(file utl_file.file_type, OUT buffer text)
AS '$libdir/whale','utl_file_get_nextline'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.get_nextline(utl_file.file_type) IS 'Returns one line from file or returns NULL';

CREATE FUNCTION utl_file.put(file utl_file.file_type, buffer text)
RETURNS bool
AS '$libdir/whale','utl_file_put'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.put(utl_file.file_type, text) IS 'Puts data to specified file';

CREATE FUNCTION utl_file.put(file utl_file.file_type, buffer anyelement)
RETURNS bool
AS $$SELECT utl_file.put($1, $2::text); $$
LANGUAGE SQL VOLATILE;
COMMENT ON FUNCTION utl_file.put(utl_file.file_type, anyelement) IS 'Puts data to specified file';

CREATE FUNCTION utl_file.new_line(file utl_file.file_type)
RETURNS bool
AS '$libdir/whale','utl_file_new_line'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.new_line(file utl_file.file_type) IS 'Function inserts one ore more newline characters in specified file';

CREATE FUNCTION utl_file.new_line(file utl_file.file_type, lines int)
RETURNS bool
AS '$libdir/whale','utl_file_new_line'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.new_line(file utl_file.file_type) IS 'Function inserts one ore more newline characters in specified file';

CREATE FUNCTION utl_file.put_line(file utl_file.file_type, buffer text)
RETURNS bool
AS '$libdir/whale','utl_file_put_line'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.put_line(utl_file.file_type, text) IS 'Puts data to specified file and append newline character';

CREATE FUNCTION utl_file.put_line(file utl_file.file_type, buffer text, autoflush bool)
RETURNS bool
AS '$libdir/whale','utl_file_put_line'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.put_line(utl_file.file_type, text, bool) IS 'Puts data to specified file and append newline character';

CREATE FUNCTION utl_file.putf(file utl_file.file_type, format text, arg1 text, arg2 text, arg3 text, arg4 text, arg5 text)
RETURNS bool
AS '$libdir/whale','utl_file_putf'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.putf(utl_file.file_type, text, text, text, text, text, text) IS 'Puts formatted data to specified file';

CREATE FUNCTION utl_file.putf(file utl_file.file_type, format text, arg1 text, arg2 text, arg3 text, arg4 text)
RETURNS bool
AS $$SELECT utl_file.putf($1, $2, $3, $4, $5, $6, NULL); $$
LANGUAGE SQL VOLATILE;
COMMENT ON FUNCTION utl_file.putf(utl_file.file_type, text, text, text, text, text) IS 'Puts formatted data to specified file';

CREATE FUNCTION utl_file.putf(file utl_file.file_type, format text, arg1 text, arg2 text, arg3 text)
RETURNS bool
AS $$SELECT utl_file.putf($1, $2, $3, $4, $5, NULL, NULL); $$
LANGUAGE SQL VOLATILE;
COMMENT ON FUNCTION utl_file.putf(utl_file.file_type, text, text, text, text) IS 'Puts formatted data to specified file';

CREATE FUNCTION utl_file.putf(file utl_file.file_type, format text, arg1 text, arg2 text)
RETURNS bool
AS $$SELECT utl_file.putf($1, $2, $3, $4, NULL, NULL, NULL); $$
LANGUAGE SQL VOLATILE;
COMMENT ON FUNCTION utl_file.putf(utl_file.file_type, text, text, text, text) IS 'Puts formatted data to specified file';

CREATE FUNCTION utl_file.putf(file utl_file.file_type, format text, arg1 text)
RETURNS bool
AS $$SELECT utl_file.putf($1, $2, $3, NULL, NULL, NULL, NULL); $$
LANGUAGE SQL VOLATILE;
COMMENT ON FUNCTION utl_file.putf(utl_file.file_type, text, text) IS 'Puts formatted data to specified file';

CREATE FUNCTION utl_file.putf(file utl_file.file_type, format text)
RETURNS bool
AS $$SELECT utl_file.putf($1, $2, NULL, NULL, NULL, NULL, NULL); $$
LANGUAGE SQL VOLATILE;
COMMENT ON FUNCTION utl_file.putf(utl_file.file_type, text) IS 'Puts formatted data to specified file';

CREATE FUNCTION utl_file.fflush(file utl_file.file_type)
RETURNS void
AS '$libdir/whale','utl_file_fflush'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.fflush(file utl_file.file_type) IS 'This procedure makes sure that all pending data for specified file is written physically out to a file';

CREATE FUNCTION utl_file.fclose(file utl_file.file_type)
RETURNS utl_file.file_type
AS '$libdir/whale','utl_file_fclose'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.fclose(utl_file.file_type) IS 'Close file';

CREATE FUNCTION utl_file.fclose_all()
RETURNS void
AS '$libdir/whale','utl_file_fclose_all'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.fclose_all() IS 'Close all open files.';

CREATE FUNCTION utl_file.fremove(location text, filename text)
RETURNS void
AS '$libdir/whale','utl_file_fremove'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.fremove(text, text) IS 'Remove file.';

CREATE FUNCTION utl_file.frename(location text, filename text, dest_dir text, dest_file text, overwrite boolean)
RETURNS void
AS '$libdir/whale','utl_file_frename'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.frename(text, text, text, text, boolean) IS 'Rename file.';

CREATE FUNCTION utl_file.frename(location text, filename text, dest_dir text, dest_file text)
RETURNS void
AS $$SELECT utl_file.frename($1, $2, $3, $4, false);$$
LANGUAGE SQL VOLATILE;
COMMENT ON FUNCTION utl_file.frename(text, text, text, text) IS 'Rename file.';

CREATE FUNCTION utl_file.fcopy(src_location text, src_filename text, dest_location text, dest_filename text)
RETURNS void
AS '$libdir/whale','utl_file_fcopy'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.fcopy(text, text, text, text) IS 'Copy a text file.';

CREATE FUNCTION utl_file.fcopy(src_location text, src_filename text, dest_location text, dest_filename text, start_line integer)
RETURNS void
AS '$libdir/whale','utl_file_fcopy'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.fcopy(text, text, text, text, integer) IS 'Copy a text file.';

CREATE FUNCTION utl_file.fcopy(src_location text, src_filename text, dest_location text, dest_filename text, start_line integer, end_line integer)
RETURNS void
AS '$libdir/whale','utl_file_fcopy'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.fcopy(text, text, text, text, integer, integer) IS 'Copy a text file.';

CREATE FUNCTION utl_file.fgetattr(location text, filename text, OUT fexists boolean, OUT file_length bigint, OUT blocksize integer)
AS '$libdir/whale','utl_file_fgetattr'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.fgetattr(text, text) IS 'Get file attributes.';

CREATE FUNCTION utl_file.tmpdir()
RETURNS text
AS '$libdir/whale','utl_file_tmpdir'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION utl_file.tmpdir() IS 'Get temp directory path.';

/* carry all safe directories */
CREATE TABLE utl_file.utl_file_dir(dir text, dirname text unique);
REVOKE ALL ON utl_file.utl_file_dir FROM PUBLIC;

/* allow only read on utl_file.utl_file_dir to unprivileged users */
GRANT SELECT ON TABLE utl_file.utl_file_dir TO PUBLIC;



-- gms_random
CREATE SCHEMA gms_random;

CREATE FUNCTION gms_random.initialize(int)
RETURNS void
AS '$libdir/whale','gms_random_initialize'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION gms_random.initialize(int) IS 'Initialize package with a seed value';

CREATE FUNCTION gms_random.normal()
RETURNS double precision
AS '$libdir/whale','gms_random_normal'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION gms_random.normal() IS 'Returns random numbers in a standard normal distribution';

CREATE FUNCTION gms_random.random()
RETURNS integer
AS '$libdir/whale','gms_random_random'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION gms_random.random() IS 'Generate Random Numeric Values';

CREATE FUNCTION gms_random.seed(integer)
RETURNS void
AS '$libdir/whale','gms_random_seed_int'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION gms_random.seed(int) IS 'Reset the seed value';

CREATE FUNCTION gms_random.seed(text)
RETURNS void
AS '$libdir/whale','gms_random_seed_varchar'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION gms_random.seed(text) IS 'Reset the seed value';

CREATE FUNCTION gms_random.string(opt text, len int)
RETURNS text
AS '$libdir/whale','gms_random_string'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION gms_random.string(text,int) IS 'Create Random Strings';

CREATE FUNCTION gms_random.terminate()
RETURNS void
AS '$libdir/whale','gms_random_terminate'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION gms_random.terminate() IS 'Terminate use of the Package';

CREATE FUNCTION gms_random.value(low double precision, high double precision)
RETURNS double precision
AS '$libdir/whale','gms_random_value_range'
LANGUAGE C STRICT VOLATILE;
COMMENT ON FUNCTION gms_random.value(double precision, double precision) IS 'Generate Random number x, where x is greater or equal to low and less then high';

CREATE FUNCTION gms_random.value()
RETURNS double precision
AS '$libdir/whale','gms_random_value'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION gms_random.value() IS 'Generate Random number x, where x is greater or equal to 0 and less then 1';

CREATE FUNCTION dump(text)
RETURNS varchar
AS '$libdir/whale', 'orafce_dump'
LANGUAGE C;

CREATE FUNCTION dump(text, integer)
RETURNS varchar
AS '$libdir/whale', 'orafce_dump'
LANGUAGE C;

CREATE FUNCTION utl_file.put_line(file utl_file.file_type, buffer anyelement)
RETURNS bool
AS $$SELECT utl_file.put_line($1, $2::text); $$
LANGUAGE SQL VOLATILE;
COMMENT ON FUNCTION utl_file.put_line(utl_file.file_type, anyelement) IS 'Puts data to specified file and append newline character';

CREATE FUNCTION utl_file.put_line(file utl_file.file_type, buffer anyelement, autoflush bool)
RETURNS bool
AS $$SELECT utl_file.put_line($1, $2::text, true); $$
LANGUAGE SQL VOLATILE;
COMMENT ON FUNCTION utl_file.put_line(utl_file.file_type, anyelement, bool) IS 'Puts data to specified file and append newline character';

CREATE FUNCTION pg_catalog.listagg1_transfn(internal, text)
RETURNS internal
AS '$libdir/whale','orafce_listagg1_transfn'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION pg_catalog.wm_concat_transfn(internal, text)
RETURNS internal
AS '$libdir/whale','orafce_wm_concat_transfn'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION pg_catalog.listagg2_transfn(internal, text, text)
RETURNS internal
AS '$libdir/whale','orafce_listagg2_transfn'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION pg_catalog.listagg_finalfn(internal)
RETURNS text
AS '$libdir/whale','orafce_listagg_finalfn'
LANGUAGE C IMMUTABLE;

/*
 * Undocumented function wm_concat - removed from
 * Orafce 12c.
 */
CREATE AGGREGATE pg_catalog.wm_concat(text) (
  SFUNC=pg_catalog.wm_concat_transfn,
  STYPE=internal,
  FINALFUNC=pg_catalog.listagg_finalfn
);

CREATE FUNCTION pg_catalog.median4_transfn(internal, real)
RETURNS internal
AS '$libdir/whale','orafce_median4_transfn'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION pg_catalog.median4_finalfn(internal)
RETURNS real
AS '$libdir/whale','orafce_median4_finalfn'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION pg_catalog.median8_transfn(internal, double precision)
RETURNS internal
AS '$libdir/whale','orafce_median8_transfn'
LANGUAGE C IMMUTABLE;

CREATE FUNCTION pg_catalog.median8_finalfn(internal)
RETURNS double precision
AS '$libdir/whale','orafce_median8_finalfn'
LANGUAGE C IMMUTABLE;

CREATE AGGREGATE pg_catalog.median(real) (
  SFUNC=pg_catalog.median4_transfn,
  STYPE=internal,
  FINALFUNC=pg_catalog.median4_finalfn
);



CREATE OR REPLACE FUNCTION pg_catalog.strposb(varchar2, varchar2) RETURNS integer
AS 'byteapos'
LANGUAGE internal
STRICT IMMUTABLE;
COMMENT ON FUNCTION pg_catalog.strposb(varchar2, varchar2) IS 'returns the byte position of a specified string in the input varchar2 string';


/* PAD */

/* LPAD family */

/* Incompatibility #1:
 *     pg_catalog.lpad removes trailing blanks of CHAR arguments
 *     because of implicit cast to text
 *
 * Incompatibility #2:
 *     pg_catalog.lpad considers character length, NOT display length
 *     so, add functions to use custom C implementation of lpad as defined
 *     in charpad.c
 */
CREATE FUNCTION orafce.lpad(char, integer, char)
RETURNS text
AS '$libdir/whale','orafce_lpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.lpad(char, integer, text)
RETURNS text
AS '$libdir/whale','orafce_lpad'
LANGUAGE 'c'
STRICT  IMMUTABLE
;

CREATE FUNCTION orafce.lpad(char, integer, varchar2)
RETURNS text
AS '$libdir/whale','orafce_lpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.lpad(char, integer, nvarchar2)
RETURNS text
AS '$libdir/whale','orafce_lpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.lpad(char, integer)
RETURNS text
AS $$ SELECT orafce.lpad($1, $2, ' '::text); $$
LANGUAGE SQL
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.lpad(text, integer, char)
RETURNS text
AS '$libdir/whale','orafce_lpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.lpad(varchar2, integer, char)
RETURNS text
AS '$libdir/whale','orafce_lpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.lpad(nvarchar2, integer, char)
RETURNS text
AS '$libdir/whale','orafce_lpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.lpad(text, integer, text)
RETURNS text
AS '$libdir/whale','orafce_lpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.lpad(text, integer, varchar2)
RETURNS text
AS '$libdir/whale','orafce_lpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.lpad(text, integer, nvarchar2)
RETURNS text
AS '$libdir/whale','orafce_lpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.lpad(text, integer)
RETURNS text
AS $$ SELECT orafce.lpad($1, $2, ' '::text); $$
LANGUAGE SQL
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.lpad(varchar2, integer, text)
RETURNS text
AS '$libdir/whale','orafce_lpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.lpad(varchar2, integer, varchar2)
RETURNS text
AS '$libdir/whale','orafce_lpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.lpad(varchar2, integer, nvarchar2)
RETURNS text
AS '$libdir/whale','orafce_lpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.lpad(varchar2, integer)
RETURNS text
AS $$ SELECT orafce.lpad($1, $2, ' '::text); $$
LANGUAGE SQL
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.lpad(nvarchar2, integer, text)
RETURNS text
AS '$libdir/whale','orafce_lpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.lpad(nvarchar2, integer, varchar2)
RETURNS text
AS '$libdir/whale','orafce_lpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.lpad(nvarchar2, integer, nvarchar2)
RETURNS text
AS '$libdir/whale','orafce_lpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.lpad(nvarchar2, integer)
RETURNS text
AS $$ SELECT orafce.lpad($1, $2, ' '::text); $$
LANGUAGE SQL
STRICT IMMUTABLE
;

/* RPAD family */

/* Incompatibility #1:
 *     pg_catalog.rpad removes trailing blanks of CHAR arguments
 *     because of implicit cast to text
 *
 * Incompatibility #2:
 *     pg_catalog.rpad considers character length, NOT display length
 *     so, add functions to use custom C implementation of rpad as defined
 *     in charpad.c
 */
CREATE FUNCTION orafce.rpad(char, integer, char)
RETURNS text
AS '$libdir/whale','orafce_rpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rpad(char, integer, text)
RETURNS text
AS '$libdir/whale','orafce_rpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rpad(char, integer, varchar2)
RETURNS text
AS '$libdir/whale','orafce_rpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rpad(char, integer, nvarchar2)
RETURNS text
AS '$libdir/whale','orafce_rpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rpad(char, integer)
RETURNS text
AS $$ SELECT orafce.rpad($1, $2, ' '::text); $$
LANGUAGE SQL
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rpad(text, integer, char)
RETURNS text
AS '$libdir/whale','orafce_rpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rpad(varchar2, integer, char)
RETURNS text
AS '$libdir/whale','orafce_rpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rpad(nvarchar2, integer, char)
RETURNS text
AS '$libdir/whale','orafce_rpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rpad(text, integer, text)
RETURNS text
AS '$libdir/whale','orafce_rpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rpad(text, integer, varchar2)
RETURNS text
AS '$libdir/whale','orafce_rpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rpad(text, integer, nvarchar2)
RETURNS text
AS '$libdir/whale','orafce_rpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rpad(text, integer)
RETURNS text
AS $$ SELECT orafce.rpad($1, $2, ' '::text); $$
LANGUAGE SQL
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rpad(varchar2, integer, text)
RETURNS text
AS '$libdir/whale','orafce_rpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rpad(varchar2, integer, varchar2)
RETURNS text
AS '$libdir/whale','orafce_rpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rpad(varchar2, integer, nvarchar2)
RETURNS text
AS '$libdir/whale','orafce_rpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rpad(varchar2, integer)
RETURNS text
AS $$ SELECT orafce.rpad($1, $2, ' '::text); $$
LANGUAGE SQL
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rpad(nvarchar2, integer, text)
RETURNS text
AS '$libdir/whale','orafce_rpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rpad(nvarchar2, integer, varchar2)
RETURNS text
AS '$libdir/whale','orafce_rpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rpad(nvarchar2, integer, nvarchar2)
RETURNS text
AS '$libdir/whale','orafce_rpad'
LANGUAGE 'c'
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rpad(nvarchar2, integer)
RETURNS text
AS $$ SELECT orafce.rpad($1, $2, ' '::text); $$
LANGUAGE SQL
STRICT IMMUTABLE
;

/* TRIM */

/* Incompatibility #1:
 *     pg_catalog.ltrim, pg_catalog.rtrim and pg_catalog.btrim remove
 *     trailing blanks of CHAR arguments because of implicit cast to
 *     text
 *
 *     Following re-definitions address this incompatbility so that
 *     trailing blanks of CHAR arguments are preserved and considered
 *     significant for the trimming process.
 */

/* LTRIM family */
CREATE FUNCTION orafce.ltrim(char, char)
RETURNS text
AS 'ltrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.ltrim(char, text)
RETURNS text
AS 'ltrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.ltrim(char, varchar2)
RETURNS text
AS 'ltrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.ltrim(char, nvarchar2)
RETURNS text
AS 'ltrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.ltrim(char)
RETURNS text
AS $$ SELECT orafce.ltrim($1, ' '::text) $$
LANGUAGE SQL
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.ltrim(text, char)
RETURNS text
AS 'ltrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.ltrim(text, text)
RETURNS text
AS 'ltrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.ltrim(text, varchar2)
RETURNS text
AS 'ltrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.ltrim(text, nvarchar2)
RETURNS text
AS 'ltrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.ltrim(text)
RETURNS text
AS $$ SELECT orafce.ltrim($1, ' '::text) $$
LANGUAGE SQL
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.ltrim(varchar2, char)
RETURNS text
AS 'ltrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.ltrim(varchar2, text)
RETURNS text
AS 'ltrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.ltrim(varchar2, varchar2)
RETURNS text
AS 'ltrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.ltrim(varchar2, nvarchar2)
RETURNS text
AS 'ltrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.ltrim(varchar2)
RETURNS text
AS $$ SELECT orafce.ltrim($1, ' '::text) $$
LANGUAGE SQL
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.ltrim(nvarchar2, char)
RETURNS text
AS 'ltrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.ltrim(nvarchar2, text)
RETURNS text
AS 'ltrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.ltrim(nvarchar2, varchar2)
RETURNS text
AS 'ltrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.ltrim(nvarchar2, nvarchar2)
RETURNS text
AS 'ltrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.ltrim(nvarchar2)
RETURNS text
AS $$ SELECT orafce.ltrim($1, ' '::text) $$
LANGUAGE SQL
STRICT IMMUTABLE
;

/* RTRIM family */
CREATE FUNCTION orafce.rtrim(char, char)
RETURNS text
AS 'rtrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rtrim(char, text)
RETURNS text
AS 'rtrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rtrim(char, varchar2)
RETURNS text
AS 'rtrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rtrim(char, nvarchar2)
RETURNS text
AS 'rtrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rtrim(char)
RETURNS text
AS $$ SELECT orafce.rtrim($1, ' '::text) $$
LANGUAGE SQL
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rtrim(text, char)
RETURNS text
AS 'rtrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rtrim(text, text)
RETURNS text
AS 'rtrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rtrim(text, varchar2)
RETURNS text
AS 'rtrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rtrim(text, nvarchar2)
RETURNS text
AS 'rtrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rtrim(text)
RETURNS text
AS $$ SELECT orafce.rtrim($1, ' '::text) $$
LANGUAGE SQL
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rtrim(varchar2, char)
RETURNS text
AS 'rtrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rtrim(varchar2, text)
RETURNS text
AS 'rtrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rtrim(varchar2, varchar2)
RETURNS text
AS 'rtrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rtrim(varchar2, nvarchar2)
RETURNS text
AS 'rtrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rtrim(varchar2)
RETURNS text
AS $$ SELECT orafce.rtrim($1, ' '::text) $$
LANGUAGE SQL
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rtrim(nvarchar2, char)
RETURNS text
AS 'rtrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rtrim(nvarchar2, text)
RETURNS text
AS 'rtrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rtrim(nvarchar2, varchar2)
RETURNS text
AS 'rtrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rtrim(nvarchar2, nvarchar2)
RETURNS text
AS 'rtrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.rtrim(nvarchar2)
RETURNS text
AS $$ SELECT orafce.rtrim($1, ' '::text) $$
LANGUAGE SQL
STRICT IMMUTABLE
;

/* BTRIM family */
CREATE FUNCTION orafce.btrim(char, char)
RETURNS text
AS 'btrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.btrim(char, text)
RETURNS text
AS 'btrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.btrim(char, varchar2)
RETURNS text
AS 'btrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.btrim(char, nvarchar2)
RETURNS text
AS 'btrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.btrim(char)
RETURNS text
AS $$ SELECT orafce.btrim($1, ' '::text) $$
LANGUAGE SQL
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.btrim(text, char)
RETURNS text
AS 'btrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.btrim(text, text)
RETURNS text
AS 'btrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.btrim(text, varchar2)
RETURNS text
AS 'btrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.btrim(text, nvarchar2)
RETURNS text
AS 'btrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.btrim(text)
RETURNS text
AS $$ SELECT orafce.btrim($1, ' '::text) $$
LANGUAGE SQL
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.btrim(varchar2, char)
RETURNS text
AS 'btrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.btrim(varchar2, text)
RETURNS text
AS 'btrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.btrim(varchar2, varchar2)
RETURNS text
AS 'btrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.btrim(varchar2, nvarchar2)
RETURNS text
AS 'btrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.btrim(varchar2)
RETURNS text
AS $$ SELECT orafce.btrim($1, ' '::text) $$
LANGUAGE SQL
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.btrim(nvarchar2, char)
RETURNS text
AS 'btrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.btrim(nvarchar2, text)
RETURNS text
AS 'btrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.btrim(nvarchar2, varchar2)
RETURNS text
AS 'btrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.btrim(nvarchar2, nvarchar2)
RETURNS text
AS 'btrim'
LANGUAGE internal
STRICT IMMUTABLE
;

CREATE FUNCTION orafce.btrim(nvarchar2)
RETURNS text
AS $$ SELECT orafce.btrim($1, ' '::text) $$
LANGUAGE SQL
STRICT IMMUTABLE
;

/* LENGTH */
CREATE FUNCTION orafce.length(char)
RETURNS integer
AS '$libdir/whale','orafce_bpcharlen'
LANGUAGE 'c'
STRICT IMMUTABLE
;

GRANT USAGE ON SCHEMA gms_output TO PUBLIC;
GRANT USAGE ON SCHEMA gms_utility TO PUBLIC;
GRANT USAGE ON SCHEMA utl_file TO PUBLIC;
GRANT USAGE ON SCHEMA gms_random TO PUBLIC;
GRANT USAGE ON SCHEMA orafce TO PUBLIC;

CREATE FUNCTION pg_catalog.round(value timestamp without time zone, fmt text)
RETURNS timestamp without time zone
AS '$libdir/whale','ora_timestamp_round'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION pg_catalog.round(timestamp with time zone, text) IS 'round dates according to the specified format';

CREATE FUNCTION pg_catalog.round(value timestamp without time zone)
RETURNS timestamp without time zone
AS $$ SELECT pg_catalog.round($1, 'DDD'); $$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION pg_catalog.round(timestamp without time zone) IS 'will round dates according to the specified format';

CREATE FUNCTION pg_catalog.trunc(value timestamp without time zone)
RETURNS timestamp without time zone
AS $$ SELECT pg_catalog.trunc($1, 'DDD'); $$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION pg_catalog.trunc(timestamp without time zone) IS 'truncate date according to the specified format';

CREATE OR REPLACE FUNCTION orafce.round(double precision, int)
RETURNS numeric
AS $$SELECT pg_catalog.round($1::numeric, $2)$$
LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION orafce.trunc(double precision, int)
RETURNS numeric
AS $$SELECT pg_catalog.trunc($1::numeric, $2)$$
LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION orafce.round(float4, int)
RETURNS numeric
AS $$SELECT pg_catalog.round($1::numeric, $2)$$
LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION orafce.trunc(float4, int)
RETURNS numeric
AS $$SELECT pg_catalog.trunc($1::numeric, $2)$$
LANGUAGE sql IMMUTABLE STRICT;


CREATE FUNCTION orafce.get_major_version()
RETURNS text
AS '$libdir/whale','ora_get_major_version'
LANGUAGE 'c' STRICT IMMUTABLE;

CREATE FUNCTION orafce.get_major_version_num()
RETURNS text
AS '$libdir/whale','ora_get_major_version_num'
LANGUAGE 'c' STRICT IMMUTABLE;

CREATE FUNCTION orafce.get_full_version_num()
RETURNS text
AS '$libdir/whale','ora_get_full_version_num'
LANGUAGE 'c' STRICT IMMUTABLE;

CREATE FUNCTION orafce.get_platform()
RETURNS text
AS '$libdir/whale','ora_get_platform'
LANGUAGE 'c' STRICT IMMUTABLE;

CREATE FUNCTION orafce.get_status()
RETURNS text
AS '$libdir/whale','ora_get_status'
LANGUAGE 'c' STRICT IMMUTABLE;

-- Orafce system views
create view orafce.user_tab_columns as
    select table_name,
           column_name,
           data_type,
           coalesce(character_maximum_length, numeric_precision) AS data_length,
           numeric_precision AS data_precision,
           numeric_scale AS data_scale,
           is_nullable AS nullable,
           ordinal_position AS column_id,
           is_updatable AS data_upgraded,
           table_schema
    from information_schema.columns;

create view orafce.user_tables as
    select table_name
      from information_schema.tables
     where table_type = 'BASE TABLE';

create view orafce.user_cons_columns as
   select constraint_name, column_name, table_name
     from information_schema.constraint_column_usage ;

create view orafce.user_constraints as
    select conname as constraint_name,
           conindid::regclass as index_name,
           case contype when 'p' then 'P' when 'f' then 'R' end as constraint_type,
           conrelid::regclass as table_name,
           case contype when 'f' then (select conname
                                         from pg_constraint c2
                                        where contype = 'p' and c2.conindid = c1.conindid)
                                      end as r_constraint_name
      from pg_constraint c1, pg_class
     where conrelid = pg_class.oid;

create view orafce.product_component_version as
    select orafce.get_major_version() as product,
           orafce.get_full_version_num() as version,
           orafce.get_platform() || ' ' || orafce.get_status() as status
    union all
    select extname,
           case when extname = 'plpgsql' then orafce.get_full_version_num() else extversion end,
           orafce.get_platform() || ' ' || orafce.get_status()
      from pg_extension;

create view orafce.user_objects as
    select relname as object_name,
           null::text as subject_name,
           c.oid as object_id,
           case relkind when 'r' then 'TABLE'
                        when 'i' then 'INDEX'
                        when 'S' then 'SEQUENCE'
                        when 'v' then 'VIEW'
                        when 'm' then 'VIEW'
                        when 'f' then 'FOREIGN TABLE' end as object_type,
           null::timestamp(0) as created,
           null::timestamp(0) as last_ddl_time,
           case when relkind = 'i' then (select case when indisvalid then 'VALID' else 'INVALID' end
                                          from pg_index
                                         where indexrelid = c.oid) end,
                                   -- else case when relispopulated then 'VALID' else 'INVALID' end end as status,
           relnamespace as namespace
      from pg_class c join pg_namespace n on c.relnamespace = n.oid
     where relkind not in  ('t','c')
       and nspname not in ('pg_toast','pg_catalog','information_schema')
    union all
    select tgname, null, t.oid, 'TRIGGER',null, null,'VALID', relnamespace
      from pg_trigger t join pg_class c on t.tgrelid = c.oid
     where not tgisinternal
    union all
    select proname, null, p.oid, 'FUNCTION', null, null, 'VALID', pronamespace
      from pg_proc p join pg_namespace n on p.pronamespace = n.oid
     where nspname not in ('pg_toast','pg_catalog','information_schema') order by 1;

create view orafce.user_procedures as
    select proname as object_name
      from pg_proc p join pg_namespace n on p.pronamespace = n.oid
       and nspname <> 'pg_catalog';

create view orafce.user_source as
    select row_number() over (partition by oid) as line, *
      from ( select oid, unnest(string_to_array(prosrc, e'\n')) as text,
                    proname as name, 'FUNCTION'::text as type
               from pg_proc) s;

create view orafce.user_views
   as select c.relname as view_name,
  pg_catalog.pg_get_userbyid(c.relowner) as owner
from pg_catalog.pg_class c
     left join pg_catalog.pg_namespace n on n.oid = c.relnamespace
where c.relkind in ('v','')
      and n.nspname <> 'pg_catalog'
      and n.nspname <> 'information_schema'
      and n.nspname !~ '^pg_toast'
  and pg_catalog.pg_table_is_visible(c.oid);

create view orafce.user_ind_columns as
    select attname as column_name, c1.relname as index_name, c2.relname as table_name
      from (select unnest(indkey) attno, indexrelid, indrelid from pg_index) s
           join pg_attribute on attno = attnum and attrelid = indrelid
           join pg_class c1 on indexrelid = c1.oid
           join pg_class c2 on indrelid = c2.oid
           join pg_namespace n on c2.relnamespace = n.oid
     where attno > 0 and nspname not in ('pg_catalog','information_schema');

CREATE VIEW orafce.dba_segments AS
SELECT
    pg_namespace.nspname AS owner,
    pg_class.relname AS segment_name,
    CASE
        WHEN pg_class.relkind = 'r' THEN CAST( 'TABLE' AS VARCHAR( 18 ) )
        WHEN pg_class.relkind = 'i' THEN CAST( 'INDEX' AS VARCHAR( 18 ) )
        WHEN pg_class.relkind = 'f' THEN CAST( 'FOREIGN TABLE' AS VARCHAR( 18 ) )
        WHEN pg_class.relkind = 'S' THEN CAST( 'SEQUENCE' AS VARCHAR( 18 ) )
        WHEN pg_class.relkind = 's' THEN CAST( 'SPECIAL' AS VARCHAR( 18 ) )
        WHEN pg_class.relkind = 't' THEN CAST( 'TOAST TABLE' AS VARCHAR( 18 ) )
        WHEN pg_class.relkind = 'v' THEN CAST( 'VIEW' AS VARCHAR( 18 ) )
        ELSE CAST( pg_class.relkind AS VARCHAR( 18 ) )
    END AS segment_type,
    spcname AS tablespace_name,
    relfilenode AS header_file,
    NULL::oid AS header_block,
    pg_relation_size( pg_class.oid ) AS bytes,
    relpages AS blocks
FROM
    pg_class
    INNER JOIN pg_namespace
     ON pg_class.relnamespace = pg_namespace.oid
    LEFT OUTER JOIN pg_tablespace
     ON pg_class.reltablespace = pg_tablespace.oid
WHERE
    pg_class.relkind not in ('f','S','v');

-- Orafce dirty functions
CREATE OR REPLACE FUNCTION orafce.lpad(int, int, int)
RETURNS text AS $$
SELECT pg_catalog.lpad($1::text,$2,$3::text)
$$ LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION orafce.lpad(bigint, int, int)
RETURNS text AS $$
SELECT pg_catalog.lpad($1::text,$2,$3::text)
$$ LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION orafce.lpad(smallint, int, int)
RETURNS text AS $$
SELECT pg_catalog.lpad($1::text,$2,$3::text)
$$ LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION orafce.lpad(numeric, int, int)
RETURNS text AS $$
SELECT pg_catalog.lpad($1::text,$2,$3::text)
$$ LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION orafce.nvl(bigint, int)
RETURNS bigint AS $$
SELECT coalesce($1, $2)
$$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION orafce.nvl(numeric, int)
RETURNS numeric AS $$
SELECT coalesce($1, $2)
$$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION orafce.nvl(int, int)
RETURNS int AS $$
SELECT coalesce($1, $2)
$$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION orafce.numtodsinterval(double precision, text)
RETURNS interval AS $$
  SELECT $1 * ('1' || $2)::interval
$$ LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION orafce.replace_empty_strings()
RETURNS TRIGGER
AS '$libdir/whale','orafce_replace_empty_strings'
LANGUAGE 'c';

CREATE OR REPLACE FUNCTION orafce.replace_null_strings()
RETURNS TRIGGER
AS '$libdir/whale','orafce_replace_null_strings'
LANGUAGE 'c';

CREATE OR REPLACE FUNCTION orafce.unistr(text)
RETURNS text
AS '$libdir/whale','orafce_unistr'
LANGUAGE 'c';

-- Translate Orafce regexp modifier into PostgreSQl ones
-- Append the global modifier if $2 is true. Used internally
-- by regexp_*() functions bellow.
CREATE OR REPLACE FUNCTION orafce.translate_orafce_modifiers(text, bool)
RETURNS text
AS $$
DECLARE
  modifiers text := '';
BEGIN
  IF $1 IS NOT NULL THEN
    -- Check that we don't have modifier not supported by Orafce
    IF $1 ~ '[^icnsmx]' THEN
      -- Modifier 's' is not supported by Orafce but it is a synonym
      -- of 'n', we translate 'n' into 's' bellow. It is safe to allow it.
      RAISE EXCEPTION 'argument ''flags'' has unsupported modifier(s).';
    END IF;
    -- Orafce 'n' modifier correspond to 's' POSIX modifier
    -- Orafce 'm' modifier correspond to 'n' POSIX modifier
    modifiers := translate($1, 'nm', 'sn');
  END IF;
  IF $2 THEN
    modifiers := modifiers || 'g';
  END IF;
  RETURN modifiers;
END;
$$
LANGUAGE plpgsql;

-- REGEXP_LIKE( string text, pattern text) -> boolean
-- If one of the param is NULL returns NULL, declared STRICT
CREATE OR REPLACE FUNCTION orafce.regexp_like(text, text)
RETURNS boolean
AS $$
  -- Orafce default behavior is newline-sensitive,
  -- PostgreSQL not, so force 'p' modifier to affect
  -- newline-sensitivity but not ^ and $ search.
  SELECT CASE WHEN (count(*) > 0) THEN true ELSE false END FROM regexp_matches($1, $2, 'p');
$$
LANGUAGE 'sql' STRICT;

-- REGEXP_LIKE( string text, pattern text, flags text ) -> boolean
CREATE OR REPLACE FUNCTION orafce.regexp_like(text, text, text)
RETURNS boolean
AS $$
DECLARE
  modifiers text;
BEGIN
  -- Only modifier can be NULL
  IF $1 IS NULL OR $2 IS NULL THEN
    RETURN NULL;
  END IF;
  modifiers := orafce.translate_orafce_modifiers($3, false);
  IF (regexp_matches($1, $2, modifiers))[1] IS NOT NULL THEN
    RETURN true;
  END IF;
  RETURN false;
END;
$$
LANGUAGE plpgsql;

-- REGEXP_COUNT( string text, pattern text ) -> integer
CREATE OR REPLACE FUNCTION orafce.regexp_count(text, text)
RETURNS integer
AS $$
  -- Orafce default behavior is newline-sensitive,
  -- PostgreSQL not, so force 'p' modifier to affect
  -- newline-sensitivity but not ^ and $ search.
  SELECT count(*)::integer FROM regexp_matches($1, $2, 'pg');
$$
LANGUAGE 'sql' STRICT;

-- REGEXP_COUNT( string text, pattern text, position int ) -> integer
CREATE OR REPLACE FUNCTION orafce.regexp_count(text, text, integer)
RETURNS integer
AS $$
DECLARE
  v_cnt integer;
BEGIN
  -- Check numeric arguments
  IF $3 < 1 THEN
    RAISE EXCEPTION 'argument ''position'' must be a number greater than 0';
  END IF;

  -- Orafce default behavior is newline-sensitive,
  -- PostgreSQL not, so force 'p' modifier to affect
  -- newline-sensitivity but not ^ and $ search.
  v_cnt :=  (SELECT count(*)::integer FROM regexp_matches(substr($1, $3), $2, 'pg'));
  RETURN v_cnt;
END;
$$
LANGUAGE plpgsql STRICT;

-- REGEXP_COUNT( string text, pattern text, position int, flags text ) -> integer
CREATE OR REPLACE FUNCTION orafce.regexp_count(text, text, integer, text)
RETURNS integer
AS $$
DECLARE
  modifiers text;
  v_cnt   integer;
BEGIN
  -- Only modifier can be NULL
  IF $1 IS NULL OR $2 IS NULL OR $3 IS NULL THEN
    RETURN NULL;
  END IF;
  -- Check numeric arguments
  IF $3 < 1 THEN
    RAISE EXCEPTION 'argument ''position'' must be a number greater than 0';
  END IF;

  modifiers := orafce.translate_orafce_modifiers($4, true);
  v_cnt := (SELECT count(*)::integer FROM regexp_matches(substr($1, $3), $2, modifiers));
  RETURN v_cnt;
END;
$$
LANGUAGE plpgsql;

--  REGEXP_INSTR( string text, pattern text ) -> integer
CREATE OR REPLACE FUNCTION orafce.regexp_instr(text, text)
RETURNS integer
AS $$
DECLARE
  v_pos integer;
  v_pattern text;
BEGIN
  -- Without subexpression specified, assume 0 which mean that the first
  -- position for the substring matching the whole pattern is returned.
  -- We need to enclose the pattern between parentheses.
  v_pattern := '(' || $2 || ')';

  -- Orafce default behavior is newline-sensitive,
  -- PostgreSQL not, so force 'p' modifier to affect
  -- newline-sensitivity but not ^ and $ search.
  v_pos := (SELECT position((SELECT (regexp_matches($1, v_pattern, 'pg'))[1] OFFSET 0 LIMIT 1) IN $1));

  -- position() returns NULL when not found, we need to return 0 instead
  IF v_pos IS NOT NULL THEN
    RETURN v_pos;
  END IF;
  RETURN 0;
END;
$$
LANGUAGE plpgsql STRICT;

--  REGEXP_INSTR( string text, pattern text, position int ) -> integer
CREATE OR REPLACE FUNCTION orafce.regexp_instr(text, text, integer)
RETURNS integer
AS $$
DECLARE
  v_pos integer;
  v_pattern text;
BEGIN
  IF $3 < 1 THEN
    RAISE EXCEPTION 'argument ''position'' must be a number greater than 0';
  END IF;
  -- Without subexpression specified, assume 0 which mean that the first
  -- position for the substring matching the whole pattern is returned.
  -- We need to enclose the pattern between parentheses.
  v_pattern := '(' || $2 || ')';

  -- Orafce default behavior is newline-sensitive,
  -- PostgreSQL not, so force 'p' modifier to affect
  -- newline-sensitivity but not ^ and $ search.
  v_pos := (SELECT position((SELECT (regexp_matches(substr($1, $3), v_pattern, 'pg'))[1] OFFSET 0 LIMIT 1) IN $1));

  -- position() returns NULL when not found, we need to return 0 instead
  IF v_pos IS NOT NULL THEN
    RETURN v_pos;
  END IF;
  RETURN 0;
END;
$$
LANGUAGE plpgsql STRICT;

--  REGEXP_INSTR( string text, pattern text, position int, occurence int ) -> integer
CREATE OR REPLACE FUNCTION orafce.regexp_instr(text, text, integer, integer)
RETURNS integer
AS $$
DECLARE
  v_pos integer;
  v_pattern text;
BEGIN
  IF $3 < 1 THEN
    RAISE EXCEPTION 'argument ''position'' must be a number greater than 0';
  END IF;
  IF $4 < 1 THEN
    RAISE EXCEPTION 'argument ''occurence'' must be a number greater than 0';
  END IF;

  -- Without subexpression specified, assume 0 which mean that the first
  -- position for the substring matching the whole pattern is returned.
  -- We need to enclose the pattern between parentheses.
  v_pattern := '(' || $2 || ')';

  -- Orafce default behavior is newline-sensitive,
  -- PostgreSQL not, so force 'p' modifier to affect
  -- newline-sensitivity but not ^ and $ search.
  v_pos := (SELECT position((SELECT (regexp_matches(substr($1, $3), v_pattern, 'pg'))[1] OFFSET $4 - 1 LIMIT 1) IN $1));

  -- position() returns NULL when not found, we need to return 0 instead
  IF v_pos IS NOT NULL THEN
    RETURN v_pos;
  END IF;
  RETURN 0;
END;
$$
LANGUAGE plpgsql STRICT;

--  REGEXP_INSTR( string text, pattern text, position int, occurence int, return_opt int ) -> integer
CREATE OR REPLACE FUNCTION orafce.regexp_instr(text, text, integer, integer, integer)
RETURNS integer
AS $$
DECLARE
  v_pos integer;
  v_len integer;
  v_pattern text;
BEGIN
  IF $3 < 1 THEN
    RAISE EXCEPTION 'argument ''position'' must be a number greater than 0';
  END IF;
  IF $4 < 1 THEN
    RAISE EXCEPTION 'argument ''occurence'' must be a number greater than 0';
  END IF;
  IF $5 != 0 AND $5 != 1 THEN
    RAISE EXCEPTION 'argument ''return_opt'' must be 0 or 1';
  END IF;

  -- Without subexpression specified, assume 0 which mean that the first
  -- Without subexpression specified, assume 0 which mean that the first
  -- position for the substring matching the whole pattern is returned.
  -- We need to enclose the pattern between parentheses.
  v_pattern := '(' || $2 || ')';

  -- Orafce default behavior is newline-sensitive,
  -- PostgreSQL not, so force 'p' modifier to affect
  -- newline-sensitivity but not ^ and $ search.
  v_pos := (SELECT position((SELECT (regexp_matches(substr($1, $3), v_pattern, 'pg'))[1] OFFSET $4-1 LIMIT 1) IN $1));

  -- position() returns NULL when not found, we need to return 0 instead
  IF v_pos IS NOT NULL THEN
    IF $5 = 1 THEN
      v_len := (SELECT length((SELECT (regexp_matches(substr($1, $3), v_pattern, 'pg'))[1] OFFSET $4 - 1 LIMIT 1)));
      v_pos := v_pos + v_len;
    END IF;
    RETURN v_pos;
  END IF;
  RETURN 0;
END;
$$
LANGUAGE plpgsql STRICT;

--  REGEXP_INSTR( string text, pattern text, position int, occurence int, return_opt int, flags text ) -> integer
CREATE OR REPLACE FUNCTION orafce.regexp_instr(text, text, integer, integer, integer, text)
RETURNS integer
AS $$
DECLARE
  v_pos integer;
  v_len integer;
  modifiers text;
  v_pattern text;
BEGIN
  -- Only modifier can be NULL
  IF $1 IS NULL OR $2 IS NULL OR $3 IS NULL OR $4 IS NULL OR $5 IS NULL THEN
    RETURN NULL;
  END IF;
  -- Check numeric arguments
  IF $3 < 1 THEN
    RAISE EXCEPTION 'argument ''position'' must be a number greater than 0';
  END IF;
  IF $4 < 1 THEN
      RAISE EXCEPTION 'argument ''occurence'' must be a number greater than 0';
  END IF;
  IF $5 != 0 AND $5 != 1 THEN
    RAISE EXCEPTION 'argument ''return_opt'' must be 0 or 1';
  END IF;
  modifiers := orafce.translate_orafce_modifiers($6, true);

  -- Without subexpression specified, assume 0 which mean that the first
  -- position for the substring matching the whole pattern is returned.
  -- We need to enclose the pattern between parentheses.
  v_pattern := '(' || $2 || ')';
  v_pos := (SELECT position((SELECT (regexp_matches(substr($1, $3), v_pattern, modifiers))[1] OFFSET $4 - 1 LIMIT 1) IN $1));

  -- position() returns NULL when not found, we need to return 0 instead
  IF v_pos IS NOT NULL THEN
    IF $5 = 1 THEN
      v_len := (SELECT length((SELECT (regexp_matches(substr($1, $3), v_pattern, modifiers))[1] OFFSET $4-1 LIMIT 1)));
      v_pos := v_pos + v_len;
    END IF;
    RETURN v_pos;
  END IF;
  RETURN 0;
END;
$$
LANGUAGE plpgsql;

--  REGEXP_INSTR( string text, pattern text, position int, occurence int, return_opt int, flags text, group int ) -> integer
CREATE OR REPLACE FUNCTION orafce.regexp_instr(text, text, integer, integer, integer, text, integer)
RETURNS integer
AS $$
DECLARE
  v_pos integer := 0;
  v_pos_orig integer := $3;
  v_len integer := 0;
  modifiers text;
  occurrence integer := $4;
  idx integer := 1;
  v_curr_pos integer := 0;
  v_pattern text;
  v_subexpr integer := $7;
BEGIN
  -- Only modifier can be NULL
  IF $1 IS NULL OR $2 IS NULL OR $3 IS NULL OR $4 IS NULL OR $5 IS NULL OR $7 IS NULL THEN
    RETURN NULL;
  END IF;
  -- Check numeric arguments
  IF $3 < 1 THEN
      RAISE EXCEPTION 'argument ''position'' must be a number greater than 0';
  END IF;
  IF $4 < 1 THEN
    RAISE EXCEPTION 'argument ''occurence'' must be a number greater than 0';
  END IF;
  IF $7 < 0 THEN
    RAISE EXCEPTION 'argument ''group'' must be a positive number';
  END IF;
  IF $5 != 0 AND $5 != 1 THEN
    RAISE EXCEPTION 'argument ''return_opt'' must be 0 or 1';
  END IF;

  -- Translate Orafce regexp modifier into PostgreSQl ones
  modifiers := orafce.translate_orafce_modifiers($6, true);

  -- If subexpression value is 0 we need to enclose the pattern between parentheses.
  IF v_subexpr = 0 THEN
     v_pattern := '(' || $2 || ')';
     v_subexpr := 1;
  ELSE
     v_pattern := $2;
  END IF;

  -- To get position of occurrence > 1 we need a more complex code
  LOOP
    v_curr_pos := v_curr_pos + v_len;
    v_pos := (SELECT position((SELECT (regexp_matches(substr($1, v_pos_orig), '('||$2||')', modifiers))[1] OFFSET 0 LIMIT 1) IN substr($1, v_pos_orig)));
    v_len := (SELECT length((SELECT (regexp_matches(substr($1, v_pos_orig), '('||$2||')', modifiers))[1] OFFSET 0 LIMIT 1)));

    EXIT WHEN v_len IS NULL;

    v_pos_orig := v_pos_orig + v_pos + v_len;
    v_curr_pos := v_curr_pos + v_pos;
    idx := idx + 1;

    EXIT WHEN idx > occurrence;
  END LOOP;

  v_pos := (SELECT position((SELECT (regexp_matches(substr($1, v_curr_pos), v_pattern, modifiers))[v_subexpr] OFFSET 0 LIMIT 1) IN substr($1, v_curr_pos)));
  IF v_pos IS NOT NULL THEN
    IF $5 = 1 THEN
      v_len := (SELECT length((SELECT (regexp_matches(substr($1, v_curr_pos), v_pattern, modifiers))[v_subexpr] OFFSET 0 LIMIT 1)));
      v_pos := v_pos + v_len;
    END IF;
    RETURN v_pos + v_curr_pos - 1;
  END IF;
  RETURN 0;
END;
$$
LANGUAGE plpgsql;

-- REGEXP_SUBSTR( string text, pattern text ) -> text
CREATE OR REPLACE FUNCTION orafce.regexp_substr(text, text)
RETURNS text
AS $$
DECLARE
  v_substr text;
  v_pattern text;
BEGIN
  -- Without subexpression specified, assume 0 which mean that the first
  -- position for the substring matching the whole pattern is returned.
  -- We need to enclose the pattern between parentheses.
  v_pattern := '(' || $2 || ')';

  -- Orafce default behavior is newline-sensitive,
  -- PostgreSQL not, so force 'p' modifier to affect
  -- newline-sensitivity but not ^ and $ search.
  v_substr := (SELECT (regexp_matches($1, v_pattern, 'pg'))[1] OFFSET 0 LIMIT 1);
  RETURN v_substr;
END;
$$
LANGUAGE plpgsql STRICT;

-- REGEXP_SUBSTR( string text, pattern text, position int ) -> text
CREATE OR REPLACE FUNCTION orafce.regexp_substr(text, text, int)
RETURNS text
AS $$
DECLARE
  v_substr text;
  v_pattern text;
BEGIN
  -- Check numeric arguments
  IF $3 < 1 THEN
    RAISE EXCEPTION 'argument ''position'' must be a number greater than 0';
  END IF;

  -- Without subexpression specified, assume 0 which mean that the first
  -- position for the substring matching the whole pattern is returned.
  -- We need to enclose the pattern between parentheses.
  v_pattern := '(' || $2 || ')';

  -- Orafce default behavior is newline-sensitive,
  -- PostgreSQL not, so force 'p' modifier to affect
  -- newline-sensitivity but not ^ and $ search.
  v_substr := (SELECT (regexp_matches(substr($1, $3), v_pattern, 'pg'))[1] OFFSET 0 LIMIT 1);
  RETURN v_substr;
END;
$$
LANGUAGE plpgsql STRICT;

-- REGEXP_SUBSTR( string text, pattern text, position int, occurence int ) -> text
CREATE OR REPLACE FUNCTION orafce.regexp_substr(text, text, integer, integer)
RETURNS text
AS $$
DECLARE
  v_substr text;
  v_pattern text;
BEGIN
  -- Check numeric arguments
  IF $3 < 1 THEN
    RAISE EXCEPTION 'argument ''position'' must be a number greater than 0';
  END IF;
  IF $4 < 1 THEN
    RAISE EXCEPTION 'argument ''occurence'' must be a number greater than 0';
  END IF;

  -- Without subexpression specified, assume 0 which mean that the first
  -- position for the substring matching the whole pattern is returned.
  -- We need to enclose the pattern between parentheses.
  v_pattern := '(' || $2 || ')';

  -- Orafce default behavior is newline-sensitive,
  -- PostgreSQL not, so force 'p' modifier to affect
  -- newline-sensitivity but not ^ and $ search.
  v_substr := (SELECT (regexp_matches(substr($1, $3), v_pattern, 'pg'))[1] OFFSET $4 - 1 LIMIT 1);
  RETURN v_substr;
END;
$$
LANGUAGE plpgsql STRICT;

-- REGEXP_SUBSTR( string text, pattern text, position int, occurence int, flags text ) -> text
CREATE OR REPLACE FUNCTION orafce.regexp_substr(text, text, integer, integer, text)
RETURNS text
AS $$
DECLARE
  v_substr text;
  v_pattern text;
  modifiers text;
BEGIN
  -- Only modifier can be NULL
  IF $1 IS NULL OR $2 IS NULL OR $3 IS NULL OR $4 IS NULL THEN
    RETURN NULL;
  END IF;
  -- Check numeric arguments
  IF $3 < 1 THEN
    RAISE EXCEPTION 'argument ''position'' must be a number greater than 0';
  END IF;
  IF $4 < 1 THEN
    RAISE EXCEPTION 'argument ''occurence'' must be a number greater than 0';
  END IF;

  modifiers := orafce.translate_orafce_modifiers($5, true);

  -- Without subexpression specified, assume 0 which mean that the first
  -- position for the substring matching the whole pattern is returned.
  -- We need to enclose the pattern between parentheses.
  v_pattern := '(' || $2 || ')';

  -- Orafce default behavior is newline-sensitive,
  -- PostgreSQL not, so force 'p' modifier to affect
  -- newline-sensitivity but not ^ and $ search.
  v_substr := (SELECT (regexp_matches(substr($1, $3), v_pattern, modifiers))[1] OFFSET $4 - 1 LIMIT 1);
  RETURN v_substr;
END;
$$
LANGUAGE plpgsql;

-- REGEXP_SUBSTR( string text, pattern text, position int, occurence int, flags text, group int ) -> text
CREATE OR REPLACE FUNCTION orafce.regexp_substr(text, text, integer, integer, text, int)
RETURNS text
AS $$
DECLARE
  v_substr text;
  v_pattern text;
  modifiers text;
  v_subexpr integer := $6;
  has_group integer;
BEGIN
  -- Only modifier can be NULL
  IF $1 IS NULL OR $2 IS NULL OR $3 IS NULL OR $4 IS NULL OR $6 IS NULL THEN
    RETURN NULL;
  END IF;
  -- Check numeric arguments
  IF $3 < 1 THEN
    RAISE EXCEPTION 'argument ''position'' must be a number greater than 0';
  END IF;
  IF $4 < 1 THEN
    RAISE EXCEPTION 'argument ''occurence'' must be a number greater than 0';
  END IF;
  IF v_subexpr < 0 THEN
    RAISE EXCEPTION 'argument ''group'' must be a positive number';
  END IF;

  -- Check that with v_subexpr = 1 we have a capture group otherwise return NULL
  has_group := (SELECT count(*) FROM regexp_matches($2, '(?:[^\\]|^)\(', 'g'));
  IF $6 = 1 AND has_group = 0 THEN
    RETURN NULL;
  END IF;

  modifiers := orafce.translate_orafce_modifiers($5, true);

  -- If subexpression value is 0 we need to enclose the pattern between parentheses.
  IF v_subexpr = 0 THEN
    v_pattern := '(' || $2 || ')';
    v_subexpr := 1;
  ELSE
    v_pattern := $2;
  END IF;

  -- Orafce default behavior is newline-sensitive,
  -- PostgreSQL not, so force 'p' modifier to affect
  -- newline-sensitivity but not ^ and $ search.
  v_substr := (SELECT (regexp_matches(substr($1, $3), v_pattern, modifiers))[v_subexpr] OFFSET $4 - 1 LIMIT 1);
  RETURN v_substr;
END;
$$
LANGUAGE plpgsql;

-- REGEXP_REPLACE( string text, pattern text, replace_string text ) -> text
CREATE OR REPLACE FUNCTION orafce.regexp_replace(text, text, text)
RETURNS text
AS $$
DECLARE
  str text;
BEGIN
  IF $2 IS NULL AND $1 IS NOT NULL THEN
    RETURN $1;
  END IF;
  -- Orafce default behavior is to replace all occurence
  -- whereas PostgreSQL only replace the first occurrence
  -- so we need to add 'g' modifier.
  SELECT pg_catalog.regexp_replace($1, $2, $3, 'g') INTO str;
  RETURN str;
END;
$$
LANGUAGE plpgsql;

-- REGEXP_REPLACE( string text, pattern text, replace_string text, position int ) -> text
CREATE OR REPLACE FUNCTION orafce.regexp_replace(text, text, text, integer)
RETURNS text
AS $$
DECLARE
  v_replaced_str text;
  v_before text;
BEGIN
  IF $1 IS NULL OR $3 IS NULL OR $4 IS NULL THEN
    RETURN NULL;
  END IF;
  IF $2 IS NULL THEN
    RETURN $1;
  END IF;
  -- Check numeric arguments
  IF $4 < 1 THEN
    RAISE EXCEPTION 'argument ''position'' must be a number greater than 0';
  END IF;

  v_before = substr($1, 1, $4 - 1);

  -- Orafce default behavior is to replace all occurence
  -- whereas PostgreSQL only replace the first occurrence
  -- so we need to add 'g' modifier.
  v_replaced_str := v_before || pg_catalog.regexp_replace(substr($1, $4), $2, $3, 'g');
  RETURN v_replaced_str;
END;
$$
LANGUAGE plpgsql;

-- REGEXP_REPLACE( string text, pattern text, replace_string text, position int, occurence int ) -> text
CREATE OR REPLACE FUNCTION orafce.regexp_replace(text, text, text, integer, integer)
RETURNS text
AS $$
DECLARE
  v_replaced_str text;
  v_pos integer := $4;
  v_before text := '';
  v_nummatch integer;
BEGIN
  IF $1 IS NULL OR $3 IS NULL OR $4 IS NULL OR $5 IS NULL THEN
    RETURN NULL;
  END IF;
  IF $2 IS NULL THEN
    RETURN $1;
  END IF;
  -- Check numeric arguments
  IF $4 < 1 THEN
    RAISE EXCEPTION 'argument ''position'' must be a number greater than 0';
  END IF;
  IF $5 < 0 THEN
    RAISE EXCEPTION 'argument ''occurrence'' must be a positive number';
  END IF;
  -- Check if the occurrence queried exceeds the number of occurrences
  IF $5 > 1 THEN
    v_nummatch := (SELECT count(*) FROM regexp_matches(substr($1, $4), $2, 'g'));
    IF $5 > v_nummatch THEN
      RETURN $1;
    END IF;
    -- Get the position of the occurrence we are looking for
    v_pos := orafce.regexp_instr($1, $2, $4, $5, 0, '', 1);
    IF v_pos = 0 THEN
      RETURN $1;
    END IF;
  END IF;
  -- Get the substring before this position we will need to restore it
  v_before := substr($1, 1, v_pos - 1);

  -- Replace all occurrences
  IF $5 = 0 THEN
    v_replaced_str := v_before || pg_catalog.regexp_replace(substr($1, v_pos), $2, $3, 'g');
  ELSE
    -- Replace the first occurrence
    v_replaced_str := v_before || pg_catalog.regexp_replace(substr($1, v_pos), $2, $3);
  END IF;

  RETURN v_replaced_str;
END;
$$
LANGUAGE plpgsql;

-- REGEXP_REPLACE( string text, pattern text, replace_string text, position int, occurence int, flags text ) -> text
CREATE OR REPLACE FUNCTION orafce.regexp_replace(text, text, text, integer, integer, text)
RETURNS text
AS $$
DECLARE
  v_replaced_str text;
  v_pos integer := $4;
  v_nummatch integer;
  v_before text := '';
  modifiers text := '';
BEGIN
  IF $1 IS NULL OR $3 IS NULL OR $4 IS NULL OR $5 IS NULL THEN
    RETURN NULL;
  END IF;
  IF $2 IS NULL THEN
    RETURN $1;
  END IF;
  -- Check numeric arguments
  IF $4 < 1 THEN
    RAISE EXCEPTION 'argument ''position'' must be a number greater than 0';
  END IF;
  IF $5 < 0 THEN
    RAISE EXCEPTION 'argument ''occurrence'' must be a positive number';
  END IF;
  -- Set the modifiers
  IF $5 = 0 THEN
    modifiers := orafce.translate_orafce_modifiers($6, true);
  ELSE
    modifiers := orafce.translate_orafce_modifiers($6, false);
  END IF;
  -- Check if the occurrence queried exceeds the number of occurrences
  IF $5 > 1 THEN
    v_nummatch := (SELECT count(*) FROM regexp_matches(substr($1, $4), $2, $6||'g'));
    IF $5 > v_nummatch THEN
      RETURN $1;
    END IF;
    -- Get the position of the occurrence we are looking for
    v_pos := orafce.regexp_instr($1, $2, $4, $5, 0, $6, 1);
    IF v_pos = 0 THEN
      RETURN $1;
    END IF;
  END IF;
  -- Get the substring before this position we will need to restore it
  v_before := substr($1, 1, v_pos - 1);
  -- Replace occurrence(s)
  v_replaced_str := v_before || pg_catalog.regexp_replace(substr($1, v_pos), $2, $3, modifiers);
  RETURN v_replaced_str;
END;
$$
LANGUAGE plpgsql;

CREATE FUNCTION bitand(bigint, bigint)
RETURNS bigint
AS $$ SELECT $1 & $2; $$
LANGUAGE sql IMMUTABLE STRICT;
CREATE SCHEMA plvstr;

CREATE FUNCTION plvstr.rvrs(str text, start_arg int, _end int)
RETURNS text
AS '$libdir/whale','plvstr_rvrs'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plvstr.rvrs(text, int, int) IS 'Reverse string or part of string';

CREATE FUNCTION plvstr.rvrs(str text, start_arg int)
RETURNS text
AS $$ SELECT plvstr.rvrs($1,$2,NULL);$$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.rvrs(text, int) IS 'Reverse string or part of string';

CREATE FUNCTION plvstr.rvrs(str text)
RETURNS text
AS $$ SELECT plvstr.rvrs($1,1,NULL);$$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.rvrs(text) IS 'Reverse string or part of string';


CREATE DOMAIN orafce.date AS timestamp(0);

CREATE OR REPLACE FUNCTION orafce.add_days_to_timestamp(orafce.date,integer)
RETURNS timestamp AS $$
SELECT $1 + interval '1 day' * $2;
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION orafce.subtract (orafce.date, integer)
RETURNS timestamp AS $$
SELECT $1 - interval '1 day' * $2;
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION orafce.add_days_to_timestamp(orafce.date,bigint)
RETURNS timestamp AS $$
SELECT $1 + interval '1 day' * $2;
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION orafce.subtract (orafce.date, bigint)
RETURNS timestamp AS $$
SELECT $1 - interval '1 day' * $2;
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION orafce.add_days_to_timestamp(orafce.date,smallint)
RETURNS timestamp AS $$
SELECT $1 + interval '1 day' * $2;
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION orafce.subtract (orafce.date, smallint)
RETURNS timestamp AS $$
SELECT $1 - interval '1 day' * $2;
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION orafce.add_days_to_timestamp(orafce.date,numeric)
RETURNS timestamp AS $$
SELECT $1 + interval '1 day' * $2;
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION orafce.subtract (orafce.date, numeric)
RETURNS timestamp AS $$
SELECT $1 - interval '1 day' * $2;
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION orafce.subtract(orafce.date,orafce.date)
RETURNS double precision AS $$
SELECT date_part('epoch', ($1::timestamp - $2::timestamp)/3600/24);
$$ LANGUAGE SQL IMMUTABLE;


CREATE OPERATOR orafce.+ (
  LEFTARG   = orafce.date,
  RIGHTARG  = bigint,
  PROCEDURE = orafce.add_days_to_timestamp
);

CREATE OPERATOR orafce.- (
  LEFTARG   = orafce.date,
  RIGHTARG  = bigint,
  PROCEDURE = orafce.subtract
);


CREATE FUNCTION orafce.add_months(TIMESTAMP WITH TIME ZONE,INTEGER)
RETURNS TIMESTAMP
AS $$ SELECT (pg_catalog.add_months($1::pg_catalog.date, $2) + $1::time)::orafce.date; $$
LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION orafce.last_day(TIMESTAMPTZ)
RETURNS TIMESTAMP
AS $$ SELECT (date_trunc('MONTH', $1) + INTERVAL '1 MONTH - 1 day' + $1::time)::orafce.date; $$
LANGUAGE SQL IMMUTABLE STRICT;


CREATE SCHEMA gms_pipe;

CREATE FUNCTION gms_pipe.pack_message(text)
RETURNS void
AS '$libdir/whale','gms_pipe_pack_message_text'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_pipe.pack_message(text) IS 'Add text field to message';

CREATE FUNCTION gms_pipe.unpack_message_text()
RETURNS text
AS '$libdir/whale','gms_pipe_unpack_message_text'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION gms_pipe.unpack_message_text() IS 'Get text fiedl from message';

CREATE FUNCTION gms_pipe.receive_message(text, int)
RETURNS int
AS '$libdir/whale','gms_pipe_receive_message'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION gms_pipe.receive_message(text, int) IS 'Receive message from pipe';

CREATE FUNCTION gms_pipe.receive_message(text)
RETURNS int
AS $$SELECT gms_pipe.receive_message($1,NULL::int);$$
LANGUAGE SQL VOLATILE;
COMMENT ON FUNCTION gms_pipe.receive_message(text) IS 'Receive message from pipe';

CREATE FUNCTION gms_pipe.send_message(text, int, int)
RETURNS int
AS '$libdir/whale','gms_pipe_send_message'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION gms_pipe.send_message(text, int, int) IS 'Send message to pipe';

CREATE FUNCTION gms_pipe.send_message(text, int)
RETURNS int
AS $$SELECT gms_pipe.send_message($1,$2,NULL);$$
LANGUAGE SQL VOLATILE;
COMMENT ON FUNCTION gms_pipe.send_message(text, int) IS 'Send message to pipe';

CREATE FUNCTION gms_pipe.send_message(text)
RETURNS int
AS $$SELECT gms_pipe.send_message($1,NULL,NULL);$$
LANGUAGE SQL VOLATILE;
COMMENT ON FUNCTION gms_pipe.send_message(text) IS 'Send message to pipe';

CREATE FUNCTION gms_pipe.unique_session_name()
RETURNS varchar
AS '$libdir/whale','gms_pipe_unique_session_name'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_pipe.unique_session_name() IS 'Returns unique session name';

CREATE FUNCTION gms_pipe.__list_pipes()
RETURNS SETOF RECORD
AS '$libdir/whale','gms_pipe_list_pipes'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_pipe.__list_pipes() IS '';

CREATE VIEW gms_pipe.db_pipes
AS SELECT * FROM gms_pipe.__list_pipes() AS (Name varchar, Items int, Size int, "limit" int, "private" bool, "owner" varchar);

CREATE FUNCTION gms_pipe.next_item_type()
RETURNS int
AS '$libdir/whale','gms_pipe_next_item_type'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_pipe.next_item_type() IS 'Returns type of next field in message';

CREATE FUNCTION gms_pipe.create_pipe(text, int, bool)
RETURNS void
AS '$libdir/whale','gms_pipe_create_pipe'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION gms_pipe.create_pipe(text, int, bool) IS 'Create named pipe';

CREATE FUNCTION gms_pipe.create_pipe(text, int)
RETURNS void
AS '$libdir/whale','gms_pipe_create_pipe_2'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION gms_pipe.create_pipe(text, int) IS 'Create named pipe';

CREATE FUNCTION gms_pipe.create_pipe(text)
RETURNS void
AS '$libdir/whale','gms_pipe_create_pipe_1'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION gms_pipe.create_pipe(text) IS 'Create named pipe';

CREATE FUNCTION gms_pipe.reset_buffer()
RETURNS void
AS '$libdir/whale','gms_pipe_reset_buffer'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION gms_pipe.reset_buffer() IS 'Clean input buffer';

CREATE FUNCTION gms_pipe.purge(text)
RETURNS void
AS '$libdir/whale','gms_pipe_purge'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_pipe.purge(text) IS 'Clean pipe';

CREATE FUNCTION gms_pipe.remove_pipe(text)
RETURNS void
AS '$libdir/whale','gms_pipe_remove_pipe'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_pipe.remove_pipe(text) IS 'Destroy pipe';

CREATE FUNCTION gms_pipe.pack_message(date)
RETURNS void
AS '$libdir/whale','gms_pipe_pack_message_date'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_pipe.pack_message(date) IS 'Add date field to message';

CREATE FUNCTION gms_pipe.unpack_message_date()
RETURNS date
AS '$libdir/whale','gms_pipe_unpack_message_date'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_pipe.unpack_message_date() IS 'Get date field from message';

CREATE FUNCTION gms_pipe.pack_message(timestamp with time zone)
RETURNS void
AS '$libdir/whale','gms_pipe_pack_message_timestamp'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_pipe.pack_message(timestamp with time zone) IS 'Add timestamp field to message';

CREATE FUNCTION gms_pipe.unpack_message_timestamp()
RETURNS timestamp with time zone
AS '$libdir/whale','gms_pipe_unpack_message_timestamp'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_pipe.unpack_message_timestamp() IS 'Get timestamp field from message';

CREATE FUNCTION gms_pipe.pack_message(numeric)
RETURNS void
AS '$libdir/whale','gms_pipe_pack_message_number'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_pipe.pack_message(numeric) IS 'Add numeric field to message';

CREATE FUNCTION gms_pipe.unpack_message_number()
RETURNS numeric
AS '$libdir/whale','gms_pipe_unpack_message_number'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_pipe.unpack_message_number() IS 'Get numeric field from message';

CREATE FUNCTION gms_pipe.pack_message(integer)
RETURNS void
AS '$libdir/whale','gms_pipe_pack_message_integer'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_pipe.pack_message(integer) IS 'Add numeric field to message';

CREATE FUNCTION gms_pipe.pack_message(bigint)
RETURNS void
AS '$libdir/whale','gms_pipe_pack_message_bigint'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_pipe.pack_message(bigint) IS 'Add numeric field to message';

CREATE FUNCTION gms_pipe.pack_message(bytea)
RETURNS void
AS '$libdir/whale','gms_pipe_pack_message_bytea'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_pipe.pack_message(bytea) IS 'Add bytea field to message';

CREATE FUNCTION gms_pipe.unpack_message_bytea()
RETURNS bytea
AS '$libdir/whale','gms_pipe_unpack_message_bytea'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_pipe.unpack_message_bytea() IS 'Get bytea field from message';

CREATE FUNCTION gms_pipe.pack_message(record)
RETURNS void
AS '$libdir/whale','gms_pipe_pack_message_record'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_pipe.pack_message(record) IS 'Add record field to message';

CREATE FUNCTION gms_pipe.unpack_message_record()
RETURNS record
AS '$libdir/whale','gms_pipe_unpack_message_record'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_pipe.unpack_message_record() IS 'Get record field from message';



-- follow package PLVdate emulation

CREATE SCHEMA plvdate;

CREATE FUNCTION plvdate.add_bizdays("date", int)
RETURNS "date"
AS '$libdir/whale','plvdate_add_bizdays'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvdate.add_bizdays("date", int) IS 'Get the date created by adding <n> business days to a date';

CREATE FUNCTION plvdate.nearest_bizday("date")
RETURNS "date"
AS '$libdir/whale','plvdate_nearest_bizday'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvdate.nearest_bizday("date") IS 'Get the nearest business date to a given date, user defined';

CREATE FUNCTION plvdate.next_bizday("date")
RETURNS "date"
AS '$libdir/whale','plvdate_next_bizday'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvdate.next_bizday("date") IS 'Get the next business date from a given date, user defined';

CREATE FUNCTION plvdate.bizdays_between(pg_catalog.date, pg_catalog.date)
RETURNS int
AS '$libdir/whale','plvdate_bizdays_between'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvdate.bizdays_between(pg_catalog.date, pg_catalog.date) IS 'Get the number of business days between two dates';

CREATE FUNCTION plvdate.prev_bizday("date")
RETURNS "date"
AS '$libdir/whale','plvdate_prev_bizday'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvdate.prev_bizday("date") IS 'Get the previous business date from a given date';

CREATE FUNCTION plvdate.isbizday("date")
RETURNS bool
AS '$libdir/whale','plvdate_isbizday'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvdate.isbizday("date") IS 'Call this function to determine if a date is a business day';

CREATE FUNCTION plvdate.set_nonbizday(text)
RETURNS void
AS '$libdir/whale','plvdate_set_nonbizday_dow'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.set_nonbizday(text) IS 'Set day of week as non bussines day';

CREATE FUNCTION plvdate.unset_nonbizday(text)
RETURNS void
AS '$libdir/whale','plvdate_unset_nonbizday_dow'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.unset_nonbizday(text) IS 'Unset day of week as non bussines day';

CREATE FUNCTION plvdate.set_nonbizday("date", bool)
RETURNS void
AS '$libdir/whale','plvdate_set_nonbizday_day'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.set_nonbizday("date", bool) IS 'Set day as non bussines day, if repeat is true, then day is nonbiz every year';

CREATE FUNCTION plvdate.unset_nonbizday("date", bool)
RETURNS void
AS '$libdir/whale','plvdate_unset_nonbizday_day'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.unset_nonbizday("date", bool) IS 'Unset day as non bussines day, if repeat is true, then day is nonbiz every year';

CREATE FUNCTION plvdate.set_nonbizday("date")
RETURNS bool
AS $$SELECT plvdate.set_nonbizday($1, false); SELECT NULL::boolean;$$
LANGUAGE SQL VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.set_nonbizday("date") IS 'Set day as non bussines day';

CREATE FUNCTION plvdate.unset_nonbizday("date")
RETURNS bool
AS $$SELECT plvdate.unset_nonbizday($1, false); SELECT NULL::boolean;$$
LANGUAGE SQL VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.unset_nonbizday("date") IS 'Unset day as non bussines day';

CREATE FUNCTION plvdate.use_easter(bool)
RETURNS void
AS '$libdir/whale','plvdate_use_easter'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.use_easter(bool) IS 'Easter Sunday and easter monday will be holiday';

CREATE FUNCTION plvdate.use_easter()
RETURNS bool
AS $$SELECT plvdate.use_easter(true); SELECT NULL::boolean;$$
LANGUAGE SQL VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.use_easter() IS 'Easter Sunday and easter monday will be holiday';

CREATE FUNCTION plvdate.unuse_easter()
RETURNS bool
AS $$SELECT plvdate.use_easter(false); SELECT NULL::boolean;$$
LANGUAGE SQL VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.unuse_easter() IS 'Easter Sunday and easter monday will not be holiday';

CREATE FUNCTION plvdate.using_easter()
RETURNS bool
AS '$libdir/whale','plvdate_using_easter'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.using_easter() IS 'Use easter?';

CREATE FUNCTION plvdate.use_great_friday(bool)
RETURNS void
AS '$libdir/whale','plvdate_use_great_friday'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.use_great_friday(bool) IS 'Great Friday will be holiday';

CREATE FUNCTION plvdate.use_great_friday()
RETURNS bool
AS $$SELECT plvdate.use_great_friday(true); SELECT NULL::boolean;$$
LANGUAGE SQL VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.use_great_friday() IS 'Great Friday will be holiday';

CREATE FUNCTION plvdate.unuse_great_friday()
RETURNS bool
AS $$SELECT plvdate.use_great_friday(false); SELECT NULL::boolean;$$
LANGUAGE SQL VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.unuse_great_friday() IS 'Great Friday will not be holiday';

CREATE FUNCTION plvdate.using_great_friday()
RETURNS bool
AS '$libdir/whale','plvdate_using_great_friday'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.using_great_friday() IS 'Use Great Friday?';

CREATE FUNCTION plvdate.include_start(bool)
RETURNS void
AS '$libdir/whale','plvdate_include_start'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.include_start(bool) IS 'Include starting date in bizdays_between calculation';

CREATE FUNCTION plvdate.include_start()
RETURNS bool
AS $$SELECT plvdate.include_start(true); SELECT NULL::boolean;$$
LANGUAGE SQL VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.include_start() IS '';

CREATE FUNCTION plvdate.noinclude_start()
RETURNS bool
AS $$SELECT plvdate.include_start(false); SELECT NULL::boolean;$$
LANGUAGE SQL VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.noinclude_start() IS '';

CREATE FUNCTION plvdate.including_start()
RETURNS bool
AS '$libdir/whale','plvdate_including_start'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.including_start() IS '';

CREATE FUNCTION plvdate.version()
RETURNS cstring
AS '$libdir/whale','plvdate_version'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.version() IS '';

CREATE FUNCTION plvdate.default_holidays(text)
RETURNS void
AS '$libdir/whale','plvdate_default_holidays'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.default_holidays(text) IS 'Load calendar for some nations';

CREATE FUNCTION plvdate.days_inmonth("date")
RETURNS integer
AS '$libdir/whale','plvdate_days_inmonth'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.days_inmonth("date") IS 'Returns number of days in month';

CREATE FUNCTION plvdate.isleapyear("date")
RETURNS bool
AS '$libdir/whale','plvdate_isleapyear'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION plvdate.isleapyear("date") IS 'Is leap year';


-- PLVstr package


CREATE FUNCTION plvstr.normalize(str text)
RETURNS varchar
AS '$libdir/whale','plvstr_normalize'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.normalize(text) IS 'Replace white chars by space, replace  spaces by space';

CREATE FUNCTION plvstr.is_prefix(str text, prefix text, cs bool)
RETURNS bool
AS '$libdir/whale','plvstr_is_prefix_text'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.is_prefix(text, text, bool) IS 'Returns true, if prefix is prefix of str';

CREATE FUNCTION plvstr.is_prefix(str text, prefix text)
RETURNS bool
AS $$ SELECT plvstr.is_prefix($1,$2,true);$$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.is_prefix(text, text) IS 'Returns true, if prefix is prefix of str';

CREATE FUNCTION plvstr.is_prefix(str int, prefix int)
RETURNS bool
AS '$libdir/whale','plvstr_is_prefix_int'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.is_prefix(int, int) IS 'Returns true, if prefix is prefix of str';

CREATE FUNCTION plvstr.is_prefix(str bigint, prefix bigint)
RETURNS bool
AS '$libdir/whale','plvstr_is_prefix_int64'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.is_prefix(bigint, bigint) IS 'Returns true, if prefix is prefix of str';

CREATE FUNCTION plvstr.substr(str text, start_arg int, len int)
RETURNS varchar
AS '$libdir/whale','plvstr_substr3'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.substr(text, int, int) IS 'Returns substring started on start_in to end';

CREATE FUNCTION plvstr.substr(str text, start_arg int)
RETURNS varchar
AS '$libdir/whale','plvstr_substr2'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.substr(text, int) IS 'Returns substring started on start_in to end';

CREATE FUNCTION plvstr.instr(str text, patt text, start_arg int, nth int)
RETURNS int
AS '$libdir/whale','plvstr_instr4'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.instr(text, text, int, int) IS 'Search pattern in string';

CREATE FUNCTION plvstr.instr(str text, patt text, start_arg int)
RETURNS int
AS '$libdir/whale','plvstr_instr3'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.instr(text, text, int) IS 'Search pattern in string';

CREATE FUNCTION plvstr.instr(str text, patt text)
RETURNS int
AS '$libdir/whale','plvstr_instr2'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.instr(text, text) IS 'Search pattern in string';

CREATE FUNCTION plvstr.lpart(str text, div text, start_arg int, nth int, all_if_notfound bool)
RETURNS text
AS '$libdir/whale','plvstr_lpart'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.lpart(text, text, int, int, bool) IS 'Call this function to return the left part of a string';

CREATE FUNCTION plvstr.lpart(str text, div text, start_arg int, nth int)
RETURNS text
AS $$ SELECT plvstr.lpart($1,$2, $3, $4, false); $$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.lpart(text, text, int, int) IS 'Call this function to return the left part of a string';

CREATE FUNCTION plvstr.lpart(str text, div text, start_arg int)
RETURNS text
AS $$ SELECT plvstr.lpart($1,$2, $3, 1, false); $$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.lpart(text, text, int) IS 'Call this function to return the left part of a string';

CREATE FUNCTION plvstr.lpart(str text, div text)
RETURNS text
AS $$ SELECT plvstr.lpart($1,$2, 1, 1, false); $$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.lpart(text, text) IS 'Call this function to return the left part of a string';

CREATE FUNCTION plvstr.rpart(str text, div text, start_arg int, nth int, all_if_notfound bool)
RETURNS text
AS '$libdir/whale','plvstr_rpart'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.rpart(text, text, int, int, bool) IS 'Call this function to return the right part of a string';

CREATE FUNCTION plvstr.rpart(str text, div text, start_arg int, nth int)
RETURNS text
AS $$ SELECT plvstr.rpart($1,$2, $3, $4, false); $$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.rpart(text, text, int, int) IS 'Call this function to return the right part of a string';

CREATE FUNCTION plvstr.rpart(str text, div text, start_arg int)
RETURNS text
AS $$ SELECT plvstr.rpart($1,$2, $3, 1, false); $$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.rpart(text, text, int) IS 'Call this function to return the right part of a string';

CREATE FUNCTION plvstr.rpart(str text, div text)
RETURNS text
AS $$ SELECT plvstr.rpart($1,$2, 1, 1, false); $$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.rpart(text, text) IS 'Call this function to return the right part of a string';

CREATE FUNCTION plvstr.lstrip(str text, substr text, num int)
RETURNS text
AS '$libdir/whale','plvstr_lstrip'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.lstrip(text, text, int) IS 'Call this function to remove characters from the beginning ';

CREATE FUNCTION plvstr.lstrip(str text, substr text)
RETURNS text
AS $$ SELECT plvstr.lstrip($1, $2, 1); $$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.lstrip(text, text) IS 'Call this function to remove characters from the beginning ';

CREATE FUNCTION plvstr.rstrip(str text, substr text, num int)
RETURNS text
AS '$libdir/whale','plvstr_rstrip'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.rstrip(text, text, int) IS 'Call this function to remove characters from the end';

CREATE FUNCTION plvstr.rstrip(str text, substr text)
RETURNS text
AS $$ SELECT plvstr.rstrip($1, $2, 1); $$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.rstrip(text, text) IS 'Call this function to remove characters from the end';



CREATE FUNCTION plvstr.swap(str text, replace text, start_arg int, length int)
RETURNS text
AS '$libdir/whale','plvstr_swap'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plvstr.swap(text,text, int, int) IS 'Replace a substring in a string with a specified string';

CREATE FUNCTION plvstr.swap(str text, replace text)
RETURNS text
AS $$ SELECT plvstr.swap($1,$2,1, NULL);$$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.swap(text,text) IS 'Replace a substring in a string with a specified string';

CREATE FUNCTION plvstr.betwn(str text, start_arg int, _end int, inclusive bool)
RETURNS text
AS '$libdir/whale','plvstr_betwn_i'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.betwn(text, int, int, bool) IS 'Find the Substring Between Start and End Locations';

CREATE FUNCTION plvstr.betwn(str text, start_arg int, _end int)
RETURNS text
AS $$ SELECT plvstr.betwn($1,$2,$3,true);$$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.betwn(text, int, int) IS 'Find the Substring Between Start and End Locations';

CREATE FUNCTION plvstr.betwn(str text, start_arg text, _end text, startnth int, endnth int, inclusive bool, gotoend bool)
RETURNS text
AS '$libdir/whale','plvstr_betwn_c'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plvstr.betwn(text, text, text, int, int, bool, bool) IS 'Find the Substring Between Start and End Locations';

CREATE FUNCTION plvstr.betwn(str text, start_arg text, _end text)
RETURNS text
AS $$ SELECT plvstr.betwn($1,$2,$3,1,1,true,false);$$
LANGUAGE SQL IMMUTABLE;
COMMENT ON FUNCTION plvstr.betwn(text, text, text) IS 'Find the Substring Between Start and End Locations';

CREATE FUNCTION plvstr.betwn(str text, start_arg text, _end text, startnth int, endnth int)
RETURNS text
AS $$ SELECT plvstr.betwn($1,$2,$3,$4,$5,true,false);$$
LANGUAGE SQL IMMUTABLE;
COMMENT ON FUNCTION plvstr.betwn(text, text, text, int, int) IS 'Find the Substring Between Start and End Locations';


CREATE SCHEMA plvchr;

CREATE FUNCTION plvchr.nth(str text, n int)
RETURNS text
AS '$libdir/whale','plvchr_nth'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvchr.nth(text, int) IS 'Call this function to return the Nth character in a string';

CREATE FUNCTION plvchr.first(str text)
RETURNS varchar
AS '$libdir/whale','plvchr_first'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvchr.first(text) IS 'Call this function to return the first character in a string';

CREATE FUNCTION plvchr.last(str text)
RETURNS varchar
AS '$libdir/whale','plvchr_last'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvchr.last(text) IS 'Call this function to return the last character in a string';

CREATE FUNCTION plvchr._is_kind(str text, kind int)
RETURNS bool
AS '$libdir/whale','plvchr_is_kind_a'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvchr._is_kind(text, int) IS '';

CREATE FUNCTION plvchr._is_kind(c int, kind int)
RETURNS bool
AS '$libdir/whale','plvchr_is_kind_i'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvchr._is_kind(int, int) IS '';

CREATE FUNCTION plvchr.is_blank(c int)
RETURNS BOOL
AS $$ SELECT plvchr._is_kind($1, 1);$$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvchr.is_blank(int) IS '';

CREATE FUNCTION plvchr.is_blank(c text)
RETURNS BOOL
AS $$ SELECT plvchr._is_kind($1, 1);$$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvchr.is_blank(text) IS '';

CREATE FUNCTION plvchr.is_digit(c int)
RETURNS BOOL
AS $$ SELECT plvchr._is_kind($1, 2);$$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvchr.is_digit(int) IS '';

CREATE FUNCTION plvchr.is_digit(c text)
RETURNS BOOL
AS $$ SELECT plvchr._is_kind($1, 2);$$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvchr.is_digit(text) IS '';

CREATE FUNCTION plvchr.is_quote(c int)
RETURNS BOOL
AS $$ SELECT plvchr._is_kind($1, 3);$$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvchr.is_quote(int) IS '';

CREATE FUNCTION plvchr.is_quote(c text)
RETURNS BOOL
AS $$ SELECT plvchr._is_kind($1, 3);$$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvchr.is_quote(text) IS '';

CREATE FUNCTION plvchr.is_other(c int)
RETURNS BOOL
AS $$ SELECT plvchr._is_kind($1, 4);$$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvchr.is_other(int) IS '';

CREATE FUNCTION plvchr.is_other(c text)
RETURNS BOOL
AS $$ SELECT plvchr._is_kind($1, 4);$$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvchr.is_other(text) IS '';

CREATE FUNCTION plvchr.is_letter(c int)
RETURNS BOOL
AS $$ SELECT plvchr._is_kind($1, 5);$$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvchr.is_letter(int) IS '';

CREATE FUNCTION plvchr.is_letter(c text)
RETURNS BOOL
AS $$ SELECT plvchr._is_kind($1, 5);$$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvchr.is_letter(text) IS '';

CREATE FUNCTION plvchr.char_name(c text)
RETURNS varchar
AS '$libdir/whale','plvchr_char_name'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvchr.char_name(text) IS '';


CREATE FUNCTION plvstr.left(str text, n int)
RETURNS varchar
AS '$libdir/whale', 'plvstr_left'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.left(text, int) IS 'Returns firs num_in charaters. You can use negative num_in';

CREATE FUNCTION plvstr.right(str text, n int)
RETURNS varchar
AS '$libdir/whale','plvstr_right'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION plvstr.right(text, int) IS 'Returns last num_in charaters. You can use negative num_ni';

CREATE FUNCTION plvchr.quoted1(str text)
RETURNS varchar
AS $$SELECT ''''||$1||'''';$$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvchr.quoted1(text) IS E'Quoted text between ''';

CREATE FUNCTION plvchr.quoted2(str text)
RETURNS varchar
AS $$SELECT '"'||$1||'"';$$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvchr.quoted2(text) IS 'Quoted text between "';

CREATE FUNCTION plvchr.stripped(str text, char_in text)
RETURNS varchar
AS $$ SELECT TRANSLATE($1, 'A'||$2, 'A'); $$
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION plvchr.stripped(text, text) IS 'Strips a string of all instances of the specified characters';

-- gms_alert

CREATE SCHEMA gms_alert;

CREATE FUNCTION gms_alert.register(name text)
RETURNS void
AS '$libdir/whale','gms_alert_register'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_alert.register(text) IS 'Register session as recipient of alert name';

CREATE FUNCTION gms_alert.remove(name text)
RETURNS void
AS '$libdir/whale','gms_alert_remove'
LANGUAGE C VOLATILE STRICT;
COMMENT ON FUNCTION gms_alert.remove(text) IS 'Remove session as recipient of alert name';

CREATE FUNCTION gms_alert.removeall()
RETURNS void
AS '$libdir/whale','gms_alert_removeall'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION gms_alert.removeall() IS 'Remove registration for all alerts';

CREATE FUNCTION gms_alert._signal(name text, message text)
RETURNS void
AS '$libdir/whale','gms_alert_signal'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION gms_alert._signal(text, text) IS '';

CREATE FUNCTION gms_alert.waitany(OUT name text, OUT message text, OUT status integer, timeout float8)
RETURNS record
AS '$libdir/whale','gms_alert_waitany'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION gms_alert.waitany(OUT text, OUT text, OUT integer, float8) IS 'Wait for any signal';

CREATE FUNCTION gms_alert.waitone(name text, OUT message text, OUT status integer, timeout float8)
RETURNS record
AS '$libdir/whale','gms_alert_waitone'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION gms_alert.waitone(text, OUT text, OUT integer, float8) IS 'Wait for specific signal';

CREATE FUNCTION gms_alert.set_defaults(sensitivity float8)
RETURNS void
AS '$libdir/whale','gms_alert_set_defaults'
LANGUAGE C VOLATILE;
COMMENT ON FUNCTION gms_alert.set_defaults(float8) IS '';

CREATE FUNCTION gms_alert.defered_signal()
RETURNS trigger
AS '$libdir/whale','gms_alert_defered_signal'
LANGUAGE C SECURITY DEFINER;
REVOKE ALL ON FUNCTION gms_alert.defered_signal() FROM PUBLIC;

CREATE FUNCTION gms_alert.signal(_event text, _message text)
RETURNS void
AS '$libdir/whale','gms_alert_signal'
LANGUAGE C SECURITY DEFINER;
COMMENT ON FUNCTION gms_alert.signal(text, text) IS 'Emit signal to all recipients';




CREATE SCHEMA plvsubst;

CREATE FUNCTION plvsubst.string(template_in text, values_in text[], subst text)
RETURNS text
AS '$libdir/whale','plvsubst_string_array'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plvsubst.string(text, text[], text) IS 'Scans a string for all instances of the substitution keyword and replace it with the next value in the substitution values list';

CREATE FUNCTION plvsubst.string(template_in text, values_in text[])
RETURNS text
AS $$SELECT plvsubst.string($1,$2, NULL);$$
LANGUAGE SQL STRICT VOLATILE;
COMMENT ON FUNCTION plvsubst.string(text, text[]) IS 'Scans a string for all instances of the substitution keyword and replace it with the next value in the substitution values list';

CREATE FUNCTION plvsubst.string(template_in text, vals_in text, delim_in text, subst_in text)
RETURNS text
AS '$libdir/whale','plvsubst_string_string'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plvsubst.string(text, text, text, text) IS 'Scans a string for all instances of the substitution keyword and replace it with the next value in the substitution values list';

CREATE FUNCTION plvsubst.string(template_in text, vals_in text)
RETURNS text
AS '$libdir/whale','plvsubst_string_string'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plvsubst.string(text, text) IS 'Scans a string for all instances of the substitution keyword and replace it with the next value in the substitution values list';

CREATE FUNCTION plvsubst.string(template_in text, vals_in text, delim_in text)
RETURNS text
AS '$libdir/whale','plvsubst_string_string'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plvsubst.string(text, text, text) IS 'Scans a string for all instances of the substitution keyword and replace it with the next value in the substitution values list';

CREATE FUNCTION plvsubst.setsubst(str text)
RETURNS void
AS '$libdir/whale','plvsubst_setsubst'
LANGUAGE C STRICT VOLATILE;
COMMENT ON FUNCTION plvsubst.setsubst(text) IS 'Change the substitution keyword';

CREATE FUNCTION plvsubst.setsubst()
RETURNS void
AS '$libdir/whale','plvsubst_setsubst_default'
LANGUAGE C STRICT VOLATILE;
COMMENT ON FUNCTION plvsubst.setsubst() IS 'Change the substitution keyword to default %s';

CREATE FUNCTION plvsubst.subst()
RETURNS text
AS '$libdir/whale','plvsubst_subst'
LANGUAGE C STRICT VOLATILE;
COMMENT ON FUNCTION plvsubst.subst() IS 'Retrieve the current substitution keyword';

CREATE SCHEMA plunit;

CREATE FUNCTION plunit.assert_true(condition boolean)
RETURNS void
AS '$libdir/whale','plunit_assert_true'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plunit.assert_true(condition boolean) IS 'Asserts that the condition is true';

CREATE FUNCTION plunit.assert_true(condition boolean, message varchar)
RETURNS void
AS '$libdir/whale','plunit_assert_true_message'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plunit.assert_true(condition boolean, message varchar) IS 'Asserts that the condition is true';

CREATE FUNCTION plunit.assert_false(condition boolean)
RETURNS void
AS '$libdir/whale','plunit_assert_false'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plunit.assert_false(condition boolean) IS 'Asserts that the condition is false';

CREATE FUNCTION plunit.assert_false(condition boolean, message varchar)
RETURNS void
AS '$libdir/whale','plunit_assert_false_message'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plunit.assert_false(condition boolean, message varchar) IS 'Asserts that the condition is false';

CREATE FUNCTION plunit.assert_null(actual anyelement)
RETURNS void
AS '$libdir/whale','plunit_assert_null'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plunit.assert_null(actual anyelement) IS 'Asserts that the actual is null';

CREATE FUNCTION plunit.assert_null(actual anyelement, message varchar)
RETURNS void
AS '$libdir/whale','plunit_assert_null_message'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plunit.assert_null(actual anyelement, message varchar) IS 'Asserts that the condition is null';

CREATE FUNCTION plunit.assert_not_null(actual anyelement)
RETURNS void
AS '$libdir/whale','plunit_assert_not_null'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plunit.assert_not_null(actual anyelement) IS 'Asserts that the actual is not null';

CREATE FUNCTION plunit.assert_not_null(actual anyelement, message varchar)
RETURNS void
AS '$libdir/whale','plunit_assert_not_null_message'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plunit.assert_not_null(actual anyelement, message varchar) IS 'Asserts that the condition is not null';

CREATE FUNCTION plunit.assert_equals(expected anyelement, actual anyelement)
RETURNS void
AS '$libdir/whale','plunit_assert_equals'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plunit.assert_equals(expected anyelement, actual anyelement) IS 'Asserts that expected and actual are equal';

CREATE FUNCTION plunit.assert_equals(expected anyelement, actual anyelement, message varchar)
RETURNS void
AS '$libdir/whale','plunit_assert_equals_message'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plunit.assert_equals(expected anyelement, actual anyelement, message varchar) IS 'Asserts that expected and actual are equal';

CREATE FUNCTION plunit.assert_equals(expected double precision, actual double precision, "range" double precision)
RETURNS void
AS '$libdir/whale','plunit_assert_equals_range'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plunit.assert_equals(expected double precision, actual double precision, "range" double precision) IS 'Asserts that expected and actual are equal';

CREATE FUNCTION plunit.assert_equals(expected double precision, actual double precision, "range" double precision, message varchar)
RETURNS void
AS '$libdir/whale','plunit_assert_equals_range_message'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plunit.assert_equals(expected double precision, actual double precision, "range" double precision, message varchar) IS 'Asserts that expected and actual are equal';

CREATE FUNCTION plunit.assert_not_equals(expected anyelement, actual anyelement)
RETURNS void
AS '$libdir/whale','plunit_assert_not_equals'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plunit.assert_not_equals(expected anyelement, actual anyelement) IS 'Asserts that expected and actual are equal';

CREATE FUNCTION plunit.assert_not_equals(expected anyelement, actual anyelement, message varchar)
RETURNS void
AS '$libdir/whale','plunit_assert_not_equals_message'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plunit.assert_not_equals(expected anyelement, actual anyelement, message varchar) IS 'Asserts that expected and actual are equal';

CREATE FUNCTION plunit.assert_not_equals(expected double precision, actual double precision, "range" double precision)
RETURNS void
AS '$libdir/whale','plunit_assert_not_equals_range'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plunit.assert_equals(expected double precision, actual double precision, "range" double precision) IS 'Asserts that expected and actual are equal';

CREATE FUNCTION plunit.assert_not_equals(expected double precision, actual double precision, "range" double precision, message varchar)
RETURNS void
AS '$libdir/whale','plunit_assert_not_equals_range_message'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plunit.assert_not_equals(expected double precision, actual double precision, "range" double precision, message varchar) IS 'Asserts that expected and actual are equal';

CREATE FUNCTION plunit.fail()
RETURNS void
AS '$libdir/whale','plunit_fail'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plunit.fail() IS 'Immediately fail.';

CREATE FUNCTION plunit.fail(message varchar)
RETURNS void
AS '$libdir/whale','plunit_fail_message'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION plunit.fail(message varchar) IS 'Immediately fail.';

-- orafce.varchar2 type support
-- openGauss has varchar2 already(fake)
CREATE FUNCTION "varchar2"(varchar2, integer, boolean)
RETURNS varchar2
AS '$libdir/whale','varchar2'
LANGUAGE C
STRICT
IMMUTABLE;

CREATE OR REPLACE FUNCTION pg_catalog.substrb(varchar2, integer, integer) RETURNS varchar2
AS 'bytea_substr'
LANGUAGE internal
STRICT IMMUTABLE;
COMMENT ON FUNCTION pg_catalog.substrb(varchar2, integer, integer) IS 'extracts specified number of bytes from the input varchar2 string starting at the specified byte position (1-based) and returns as a varchar2 string';

CREATE OR REPLACE FUNCTION pg_catalog.substrb(varchar2, integer) RETURNS varchar2
AS 'bytea_substr_no_len'
LANGUAGE internal
STRICT IMMUTABLE;
COMMENT ON FUNCTION pg_catalog.substrb(varchar2, integer) IS 'extracts specified number of bytes from the input varchar2 string starting at the specified byte position (1-based) and returns as a varchar2 string';

CREATE OR REPLACE FUNCTION pg_catalog.lengthb(varchar2) RETURNS integer
AS 'byteaoctetlen'
LANGUAGE internal
STRICT IMMUTABLE;
COMMENT ON FUNCTION pg_catalog.lengthb(varchar2) IS 'returns byte length of the input varchar2 string';


-- orafce.nvarchar2 type support

CREATE FUNCTION nvarchar2_transform(internal)
RETURNS internal
AS '$libdir/whale','orafce_varchar_transform'
LANGUAGE C
STRICT
IMMUTABLE;

CREATE FUNCTION nvarchar2send(nvarchar2)
RETURNS bytea
AS 'varcharsend'
LANGUAGE internal
STRICT
STABLE;

CREATE FUNCTION nvarchar2typmodin(cstring[])
RETURNS integer
AS 'varchartypmodin'
LANGUAGE internal
STRICT
IMMUTABLE;

CREATE FUNCTION nvarchar2typmodout(integer)
RETURNS CSTRING
AS 'varchartypmodout'
LANGUAGE internal
STRICT
IMMUTABLE;


CREATE FUNCTION orafce.orafce_concat2(varchar2, varchar2)
RETURNS varchar2
AS '$libdir/whale','orafce_concat2'
LANGUAGE C STABLE;

CREATE FUNCTION orafce.orafce_concat2(nvarchar2, nvarchar2)
RETURNS nvarchar2
AS '$libdir/whale','orafce_concat2'
LANGUAGE C IMMUTABLE;

-- gms_assert

CREATE SCHEMA gms_assert;

CREATE FUNCTION gms_assert.enquote_literal(str varchar)
RETURNS varchar
AS '$libdir/whale','gms_assert_enquote_literal'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION gms_assert.enquote_literal(varchar) IS 'Add leading and trailing quotes, verify that all single quotes are paired with adjacent single quotes';

CREATE FUNCTION gms_assert.enquote_name(str varchar, loweralize boolean)
RETURNS varchar
AS '$libdir/whale','gms_assert_enquote_name'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION gms_assert.enquote_name(varchar, boolean) IS 'Enclose name in double quotes';

CREATE FUNCTION gms_assert.enquote_name(str varchar)
RETURNS varchar
AS 'SELECT gms_assert.enquote_name($1, true)'
LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION gms_assert.enquote_name(varchar) IS 'Enclose name in double quotes';

CREATE FUNCTION gms_assert.noop(str varchar)
RETURNS varchar
AS '$libdir/whale','gms_assert_noop'
LANGUAGE C IMMUTABLE STRICT;
COMMENT ON FUNCTION gms_assert.noop(varchar) IS 'Returns value without any checking.';

CREATE FUNCTION gms_assert.schema_name(str varchar)
RETURNS varchar
AS '$libdir/whale','gms_assert_schema_name'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION gms_assert.schema_name(varchar) IS 'Verify input string is an existing schema name.';

CREATE FUNCTION gms_assert.object_name(str varchar)
RETURNS varchar
AS '$libdir/whale','gms_assert_object_name'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION gms_assert.object_name(varchar) IS 'Verify input string is an existing object name.';

CREATE FUNCTION gms_assert.simple_sql_name(str varchar)
RETURNS varchar
AS '$libdir/whale','gms_assert_simple_sql_name'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION gms_assert.object_name(varchar) IS 'Verify input string is an sql name.';

CREATE FUNCTION gms_assert.qualified_sql_name(str varchar)
RETURNS varchar
AS '$libdir/whale','gms_assert_qualified_sql_name'
LANGUAGE C IMMUTABLE;
COMMENT ON FUNCTION gms_assert.object_name(varchar) IS 'Verify input string is an qualified sql name.';


CREATE FUNCTION orafce.sysdate()
RETURNS orafce.date
AS '$libdir/whale','orafce_sysdate'
LANGUAGE C STABLE STRICT;
COMMENT ON FUNCTION orafce.sysdate() IS 'Ruturns statement timestamp at server time zone';

/* CREATE CAST */

CREATE CAST (nvarchar2 AS real)
WITH INOUT
AS IMPLICIT;

CREATE CAST (nvarchar2 AS double precision)
WITH INOUT
AS IMPLICIT;

CREATE CAST (nvarchar2 AS smallint)
WITH INOUT
AS IMPLICIT;

CREATE CAST (nvarchar2 AS bigint)
WITH INOUT
AS IMPLICIT;

CREATE CAST (nvarchar2 AS interval)
WITH INOUT
AS IMPLICIT;


CREATE FUNCTION orafce.next_day(TIMESTAMP WITH TIME ZONE,INTEGER)
RETURNS TIMESTAMP
AS $$ SELECT (pg_catalog.next_day($1::pg_catalog.date,$2) + $1::time)::orafce.date; $$
LANGUAGE SQL IMMUTABLE STRICT;

CREATE FUNCTION orafce.next_day(TIMESTAMP WITH TIME ZONE,TEXT)
RETURNS TIMESTAMP
AS $$ SELECT (pg_catalog.next_day($1::pg_catalog.date,$2) + $1::time)::orafce.date; $$
LANGUAGE SQL IMMUTABLE STRICT;


/*
 * Note - a procedure keyword is depraceted from PostgreSQL 11, but it used
 * because older release doesn't know function.
 *
 */
CREATE OPERATOR || (procedure = orafce.orafce_concat2, leftarg = varchar2, rightarg = varchar2);
CREATE OPERATOR || (procedure = orafce.orafce_concat2, leftarg = nvarchar2, rightarg = nvarchar2);

GRANT USAGE ON SCHEMA gms_pipe TO PUBLIC;
GRANT USAGE ON SCHEMA gms_alert TO PUBLIC;
GRANT USAGE ON SCHEMA plvdate TO PUBLIC;
GRANT USAGE ON SCHEMA plvstr TO PUBLIC;
GRANT USAGE ON SCHEMA plvchr TO PUBLIC;
GRANT USAGE ON SCHEMA plvsubst TO PUBLIC;
GRANT USAGE ON SCHEMA plunit TO PUBLIC;
GRANT USAGE ON SCHEMA gms_assert TO PUBLIC;
GRANT SELECT ON gms_pipe.db_pipes to PUBLIC;


/* orafce 3.3. related changes */
ALTER FUNCTION gms_assert.enquote_name ( character varying ) STRICT;
ALTER FUNCTION gms_assert.enquote_name ( character varying, boolean ) STRICT;
ALTER FUNCTION gms_assert.noop ( character varying ) STRICT;

CREATE OR REPLACE FUNCTION pg_catalog.to_number(numeric)
RETURNS numeric AS $$
SELECT pg_catalog.to_number($1::text);
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION pg_catalog.to_number(numeric,numeric)
RETURNS numeric AS $$
SELECT pg_catalog.to_number($1::text,$2::text);
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION orafce.to_date(TEXT)
RETURNS orafce.date
AS $$ SELECT pg_catalog.to_date($1)::orafce.date; $$
LANGUAGE SQL STABLE STRICT;

CREATE OR REPLACE FUNCTION orafce.to_date(TEXT,TEXT)
RETURNS orafce.date
AS $$ SELECT TO_TIMESTAMP($1,$2)::orafce.date; $$
LANGUAGE SQL IMMUTABLE STRICT;
